[Dbix-class] Access to the database connection object

[brett gardner]

ahh, I was not aware that you could put where args into the "ON (...)" 
part of the statement.

Thanks.

Brandon Black wrote:

>On 6/19/06, brett gardner <brett at clientcomm.com.au> wrote:
>  
>
>>Okay here it is.
>>
>>    my $sql = 'SELECT firms.user_id ' .
>>        'FROM firms LEFT JOIN (' .
>>            'SELECT firm_id,allocated_requested_query_id ' .
>>            'FROM allocated_requested_queries ' .
>>            'WHERE requested_query_id=?' .
>>        ') AS restricted_alloc_queries ON
>>restricted_alloc_queries.firm_id = firms.user_id ' .
>>        'WHERE allocated_requested_query_id IS NULL ';
>>
>>    
>>
>
>Couldn't this be transformed to the (simpler and probably more optimal?):
>
>SELECT f.user_id FROM firms f LEFT JOIN allocated_requested_queries
>raq ON ( raq.firm_id = f.user_id AND raq.requested_query_id = ? )
>WHERE raq.allocated_requested_query_id IS NULL
>
>(Not that this neccesarily helps with DBIC, as I don't believe we
>support such a complex join yet either)
>
>  
>
>>    #now add the SQL that checks the firm offers all the services in the
>>requested
>>    #postcode
>>    while ($services->next_item){
>>        push @bind_vals,($query->postcode_id,$services->service_id);
>>        $sql .= ' AND EXISTS (' .
>>            'SELECT 1 ' .
>>            'FROM ownership ' .
>>            'WHERE postcode_id=? ' .
>>                'AND service_id = ? ' .
>>                'AND ownership.firm_id = firms.user_id ' .
>>        ') ';
>>    }
>>    
>>
>
>I suspect this can be re-written without subselects by instead
>inserting a "LEFT JOIN ownership ..." into the query and moving WHERE
>args to ON args as appropriate.
>
>-- Brandon
>
>_______________________________________________
>List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
>Wiki: http://dbix-class.shadowcatsystems.co.uk/
>IRC: irc.perl.org#dbix-class
>SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
>
>  
>

-- 
Brett Gardner
Clientcomm
Phone : 02 9699 1888
Fax   : 02 9318 6499