[Dbix-class] Access to the database connection object

[brett gardner]

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 ';

    #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 ' .
        ') ';
    }

Matt S Trout wrote:

>brett gardner wrote:
>  
>
>>Is there anyway to get access to the database connection object used by 
>>Storage to run a literal sql command.
>>
>>I have an extremely complicated SQL query that involving a left join on 
>>a sub query, and then multiple exists on sub queries that I cannot for 
>>the life of me get to work using SQL::Abstract.
>>    
>>
>
>Hey, no fair! You never gave *us* a chance to get it work using SQL::Abstract :)
>
>Send details of the query please. The answer *may* be "yeah ok, get the dbh" 
>but even if it is for now I have a subselects branch brewing (hopefully for 
>08) I can add this to the torture tests for.
>
>  
>

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