[Dbix-class] Access to the database connection object

[Brandon Black]

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