Query Path Join with 1.2

12 views
Skip to first unread message

Alper Akgün

unread,
Oct 3, 2013, 6:26:57 AM10/3/13
to datam...@googlegroups.com
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-properly
b) DISTINCT(expenses.id) type of construct seems to be unsupported.



Reply all
Reply to author
Forward
0 new messages