[Dbix-class] subquery ain't workin'
Jarom Smith
jarom at jaromsmith.net
Thu Jun 4 02:26:57 GMT 2009
I'm having problems with a subquery -- what am I doing wrong?
output is:
-------------------
jarom at abby:~/EzTurns >test.pl
Can't locate object method "as_query" via package
"DBIx::Class::ResultSetColumn" at ./test.pl line 27.
code is:
-------------------
my $property_id = 7;
# get the list of services for this property (alphabetical order)
my $turnover_needs = $schema->resultset('EzTurnoverNeedsProperty')->search(
{
'property_id' => $property_id,
},
);
my $missing_needs = $schema->resultset('EzTurnoverNeeds')->search(
{
'id' => { 'NOT IN' =>
$turnover_needs->get_column('turnover_need_id')->as_query },
},
{
order_by => 'description'
}
);
DB is:
-------------------
mysql> desc ez_turnover_needs_property;
+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| turnover_need_id | int(10) unsigned | NO | PRI | 0 | |
| property_id | int(10) unsigned | NO | PRI | 0 | |
| sort_order | tinyint(4) | NO | | | |
+------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from ez_turnover_needs_property;
+------------------+-------------+------------+
| turnover_need_id | property_id | sort_order |
+------------------+-------------+------------+
| 2 | 7 | 127 |
| 2 | 8 | 127 |
| 3 | 9 | 127 |
| 4 | 7 | 3 |
| 5 | 8 | 4 |
| 6 | 9 | 127 |
| 11 | 9 | 127 |
| 14 | 9 | 127 |
| 15 | 7 | 10 |
| 15 | 8 | 127 |
| 16 | 7 | 2 |
| 19 | 9 | 127 |
| 25 | 9 | 127 |
| 33 | 9 | 127 |
| 35 | 8 | 127 |
+------------------+-------------+------------+
15 rows in set (0.00 sec)
mysql> desc ez_turnover_needs;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| description | varchar(50) | YES | | NULL | |
| sort_order | tinyint(4) | YES | | NULL | |
| image_name | varchar(20) | NO | | | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from ez_turnover_needs;
+----+---------------------------------+------------+------------+
| id | description | sort_order | image_name |
+----+---------------------------------+------------+------------+
| 1 | General Cleaning | 1 | cleaning |
| 2 | Carpet Cleaning | 2 | carpetcln |
| 3 | Painting | 3 | painting |
| 4 | General Maintenance | 4 | maint |
| 5 | Pest Control | 5 | pestctrl |
| 6 | New Carpet Installation | 6 | nucarpet |
| 7 | Wood or Laminate Flooring | 7 | woodflr |
| 8 | Cabinet Replacement/Refinishing | 8 | cabinets |
| 9 | Furniture Rental | 9 | furniture |
| 10 | Plumbing | 10 | plumbing |
| 11 | Electrical | 11 | electrical |
| 12 | Drywall Repair | 12 | drywall |
| 13 | Landscaping | 13 | landscape |
| 14 | Air Duct Cleaning | 14 | airduct |
| 15 | Carpet Patch or Repair | 15 | repaircrpt |
| 16 | Tile Installation or Repair | 16 | tilework |
| 17 | Reseal | 17 | reseal |
| 18 | Powerwashing | 18 | powerwsh |
| 19 | New Vinyl Floor Installation | 19 | nuvinyl |
| 20 | Granite Counter Installation | 20 | granite |
| 21 | Install New Appliance | 21 | appliance |
| 22 | Refrigerator Rental | 22 | fridge |
| 23 | Heating and Air Conditioning | 23 | hvac |
| 24 | Re-Key Locks | 24 | rekey |
| 25 | Other | 25 | other |
| 26 | Screen Repair and Replacement | 26 | screens |
| 27 | Initial Inspection | 27 | walkthru |
| 28 | Final Inspection | 28 | fnlwalk |
| 29 | Roof Repairs | 29 | roofing |
| 30 | Installation of Upgrades | 30 | upgrade |
| 31 | Gutter Cleaning | 31 | gutter |
| 32 | Window or Glass Replacement | 32 | window |
| 33 | Mold Removal | 33 | mold |
| 34 | Repair Stucco | 34 | stucco |
| 35 | Brick and Block Work | 35 | masonry |
| 36 | Concrete Addition or Repair | 36 | concrete |
+----+---------------------------------+------------+------------+
36 rows in set (0.00 sec)
What I'm trying to get is:
-------------------
mysql> select * from ez_turnover_needs where id not in (select
turnover_need_id from ez_turnover_needs_property where property_id = 7);
+----+---------------------------------+------------+------------+
| id | description | sort_order | image_name |
+----+---------------------------------+------------+------------+
| 1 | General Cleaning | 1 | cleaning |
| 3 | Painting | 3 | painting |
| 5 | Pest Control | 5 | pestctrl |
| 6 | New Carpet Installation | 6 | nucarpet |
| 7 | Wood or Laminate Flooring | 7 | woodflr |
| 8 | Cabinet Replacement/Refinishing | 8 | cabinets |
| 9 | Furniture Rental | 9 | furniture |
| 10 | Plumbing | 10 | plumbing |
| 11 | Electrical | 11 | electrical |
| 12 | Drywall Repair | 12 | drywall |
| 13 | Landscaping | 13 | landscape |
| 14 | Air Duct Cleaning | 14 | airduct |
| 17 | Reseal | 17 | reseal |
| 18 | Powerwashing | 18 | powerwsh |
| 19 | New Vinyl Floor Installation | 19 | nuvinyl |
| 20 | Granite Counter Installation | 20 | granite |
| 21 | Install New Appliance | 21 | appliance |
| 22 | Refrigerator Rental | 22 | fridge |
| 23 | Heating and Air Conditioning | 23 | hvac |
| 24 | Re-Key Locks | 24 | rekey |
| 25 | Other | 25 | other |
| 26 | Screen Repair and Replacement | 26 | screens |
| 27 | Initial Inspection | 27 | walkthru |
| 28 | Final Inspection | 28 | fnlwalk |
| 29 | Roof Repairs | 29 | roofing |
| 30 | Installation of Upgrades | 30 | upgrade |
| 31 | Gutter Cleaning | 31 | gutter |
| 32 | Window or Glass Replacement | 32 | window |
| 33 | Mold Removal | 33 | mold |
| 34 | Repair Stucco | 34 | stucco |
| 35 | Brick and Block Work | 35 | masonry |
| 36 | Concrete Addition or Repair | 36 | concrete |
+----+---------------------------------+------------+------------+
I can do this w/o a subquery by building a query with a bunch of "!="
for each element of "not in" but it would probably be simpler just to
get everything and discard the stuff I don't want... mostly I'm just
curious why I can't get this to work.
jarom smith
tech go-to guy
More information about the DBIx-Class
mailing list