Greetings!
Yes, I understand this solution. So there is no solution I want.
Thank you!
On Mon, Jul 1, 2013 at 11:18 PM, Rob Kinyon <rob.kin...@gmail.com> wrote:
> I've been told I should elaborate.
>
> distinct=>1 doesn't require that you specify the selected columns in order
> to work. "distinct=>1" (modulo a few nits) is really sugar for "group_by
> over all selected columns".
>
> The key here is that you often don't realize you're not getting as
> distinct as you really want. An example may help.
>
> Let's say you have two tables - orders and order_lineitems with the
> obvious 1:N relationship. You're being asked to get a list of all cities
> and states that have an order containing an item that's dangerous to ship.
> "Dangerous to ship" is one of three types. So, the SQL would be:
>
> SELECT orders.city, orders.state
> FROM orders
> JOIN lineitems ON (orders.id = lineitems.order_id)
> WHERE lineitems.type IN (1, 3, 9)
> GROUP BY orders.city, orders.state
>
> The naive solution in DBIC would be:
>
> $schema->resultset('orders')->search({
> 'lineitems.type' => [ 1, 3, 9 ],
> }, {
> join => 'lineitems',
> distinct => 1,
> });
>
> And that will give you a resultset with EVERY order that has a lineitem
> with those items. Even when you did distinct=>1. The SQL you'll get will
> look something like:
>
> SELECT me.id, me.date, me.city, me.state, me.zip, me.amount, ...
> FROM orders me
> JOIN lineitems ON (orders.id = lineitems.order_id)
> WHERE lineitems.type IN (1, 3, 9)
> GROUP BY me.id, me.date, me.city, me.state, me.zip, me.amount, ...
>
> Adding the "select => [ 'me.city', 'me.state' ]" changes the SELECT
> clause, which then changes the GROUP BY clause, giving you what you want,
> as so:
> $schema->resultset('orders')->search({
> 'lineitems.type' => [ 1, 3, 9 ],
> }, {
> join => 'lineitems',
> 'select' => [ 'me.city', 'me.state' ],
> distinct => 1,
> });
>
>
> Alternately, you could do:
> $schema->resultset('orders')->search({
> 'lineitems.type' => [ 1, 3, 9 ],
> }, {
> join => 'lineitems',
> group_by => [ 'me.city', 'me.state' ],
> });
>
> This is less desirable from both a SQL execution perspective (which rows
> does the engine pick and why?) and Perl performance perspective (what if
> there are several large columns?). Also, it obfuscates the developer intent
> - the rows could be used for some other purpose which could cause
> heisenbugs. Much better to write it with the select columns clearly
> specified. That way, DBIC doesn't fill the other attributes.
>
> Rob
>
>
>
> On Mon, Jul 1, 2013 at 8:57 AM, Rob Kinyon <rob.kin...@gmail.com> wrote:
>
>> For this to work, you'd need to specify which columns you're returning.
>> So, something like:
>>
>> $schema->resultset('First')->search({
>> 'second.attr' => { -in => [ 1, 2, 3 ] },
>> }, {
>> join => 'second',
>> select => [ 'me.id', 'me.attr1', 'me.attr2' ],
>> distinct => 1,
>> });
>>
>> Note: this means you won't be able to do $row->attr3 like you'd normally
>> be able to. Because, well, you didn't get it back. :)
>>
>>
>> On Mon, Jul 1, 2013 at 8:50 AM, Bill Moseley <mose...@hank.org> wrote:
>>
>>>
>>>
>>> On Mon, Jul 1, 2013 at 6:57 AM, Dmitry Belyavsky <beld...@gmail.com>wrote:
>>>
>>>> Greating!
>>>>
>>>> I have 2 tables having one-to-many relation.
>>>>
>>>> I want to get all data from the 1st table filtered by an attr from the
>>>> 2nd one.
>>>>
>>>> So I write
>>>>
>>>> $schema->resultset("First")->search({seconds.attr => {-in => [1,2,3]}},
>>>> {join => second} );
>>>>
>>>> The resulting queue does not contain the DISTINCT keyword so I get much
>>>> more records in the resultset than I want.
>>>>
>>>> So how can I enforce the DBIx::Class to write a query like
>>>>
>>>> Select distinct me.id, me.attr1, me.attr2
>>>> from first me
>>>> left join second ON me.id=second.first_id
>>>> where second.attr not in (1,2,3)
>>>>
>>>
>>> Is this what you are after?
>>>
>>>
>>> http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/ResultSet.pm#distinct
>>>
>>>
>>>
>>> --
>>> Bill Moseley
>>> mose...@hank.org
>>> _______________________________________________
>>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>>> IRC: irc.perl.org#dbix-class
>>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>>> Searchable Archive:
>>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>>
>>
>>
>>
>> --
>> Thanks,
>> Rob Kinyon
>>
>
>
>
> --
> Thanks,
> Rob Kinyon
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
--
SY, Dmitry Belyavsky
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
