Hello all,
I am having problems with a query by an association(collection) using the two alternatives described in documentation (
http://datamapper.org/docs/find.html).
Does anyone have any suggestions without writing a custom SQL query? (mysql 5.*, dm-* 1.2)
1. Order.all(:limit=>20, Order.order_lines.color=>'blue')
# by query path; this makes an inner join and returns *multiple orders* if more than one blue order_line exist in each order. But i need unique list of orders.
2. Order.all(:limit=>20, :order_lines=>{:color=>'blue'})
# by association hash; this makes 2 queries to the mysql; it first selects all
orders.id from order_lines (10 millions) with
# SELECT `order_id` FROM `order_lines` WHERE `order_id` = 'blue'
# and then queries orders table with "FROM orders where id in (12 1232 2323....)" with around a million blue item ids!!
Notes
a) When attempting a where clause with IN subquery,I saw this; #
http://datamapper.lighthouseapp.com/projects/20609/tickets/1330-dm-doesnt-handle-sub-selects-properlyb) DISTINCT(
expenses.id) type of construct seems to be unsupported.