Re: [RDBO] negative relationships?
Hello John, I did some more testing, got it working but still don't understand everything. This should work: $vendors = Vendor::Manager-get_vendors( with_objects = [ 'products' ], query= [ 'products.vendor_id' = undef ]); Yes, this works fine. That may not be the most efficient way to do this query, however. Throwing a hard-coded SQL sub-query into the WHERE clause might be faster: SELECT ... FROM vendors v WHERE NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id) You'd use the clauses param to do that: $subselect = 'NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id)'; $vendors = Vendor::Manager-get_vendors(clauses = [ $subselect ]); This doesn't work for me (result list is empty but should have two entries). If I change the $subselect to Cees' 'id NOT IN (SELECT vendor_id FROM products)' it works. Is this perhaps a limitation of SQLite? Cheers, Michael --- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnkkid=110944bid=241720dat=121642 ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] negative relationships?
On 3/25/06 10:44 AM, Michael Lackhoff wrote: $subselect = 'NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id)'; $vendors = Vendor::Manager-get_vendors(clauses = [ $subselect ]); This doesn't work for me (result list is empty but should have two entries). If I change the $subselect to Cees' 'id NOT IN (SELECT vendor_id FROM products)' it works. Is this perhaps a limitation of SQLite? Yeah, maybe. I tested it in Pg, IIRC. Maybe SQLite (or your version of SQLite) doesn't like that syntax. You can always try setting $Rose::DB::Object::Manager::Debug = 1 to see the actual SQL generated, then run that SQL manually against your SQLite database. They'll tell you for sure what the problem is. -John --- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnkkid=110944bid=241720dat=121642 ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] negative relationships?
Just for the record, I found it. 'NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id)'; $vendors = Vendor::Manager-get_vendors(clauses = [ $subselect ]); This doesn't work for me (result list is empty but should have two entries). If I change the $subselect to Cees' 'id NOT IN (SELECT vendor_id FROM products)' it works. Is this perhaps a limitation of SQLite? Yeah, maybe. I tested it in Pg, IIRC. Maybe SQLite (or your version of SQLite) doesn't like that syntax. You can always try setting $Rose::DB::Object::Manager::Debug = 1 to see the actual SQL generated, then run that SQL manually against your SQLite database. They'll tell you for sure what the problem is. Both tables have an 'id' column, so I had to use 'vendor.id', instead of just 'id'. Michael --- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnkkid=110944bid=241720dat=121642 ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] negative relationships?
On 3/24/06, Michael Lackhoff [EMAIL PROTECTED] wrote: Say, I have two tables: vendors and products. Now I want a list of all the vendors that have no products yet. This can be seen from the products table if there are no records with the vendor_id of the vendor in question. Is this possible with a 'normal' manager method or with a relationship This should work: $vendors = Vendor::Manager-get_vendors( with_objects = [ 'products' ], query= [ 'products.vendor_id' = undef ]); producing a query like this: SELECT ... FROM vendors t1 LEFT OUTER JOIN products t2 ON (t1.id = t2.vendor_id) WHERE t2.vendor_id IS NULL That may not be the most efficient way to do this query, however. Throwing a hard-coded SQL sub-query into the WHERE clause might be faster: SELECT ... FROM vendors v WHERE NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id) You'd use the clauses param to do that: $subselect = 'NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id)'; $vendors = Vendor::Manager-get_vendors(clauses = [ $subselect ]); And if I have to hand-code it with get_objects_from_sql -- can you give me a hint, what would be a good (fast, portable) SQL to do this kind of query (my SQL isn't that good). It all depends on your database and your data. Try the two approaches above and see which one is more efficient for you...in terms of both the db and the maintainability and understandability of the code. There are always trade-offs :) And how can I make the resulting method a method of my Vendors class (I don't use separate manager classes)? Both of the above approaches should be possible in a relationship method by using the manager_args and query_args relationship parameters. -John --- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnkkid0944bid$1720dat1642 ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] negative relationships?
Ha, Cees is a faster typist I guess... :) -John --- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnkkid0944bid$1720dat1642 ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
