[Dbix-class] Complex Query to generate at runtime

[Neil Lunn]

Neil Lunn wrote:
>
> I am trying to set up a complex query to generate at runtime and 
> return through a resultset. My approach to this would normally be  
> looking at building the SQL statement and sending it off of execution.
>
> The question here, is there a way to build arbitary SQL at runtime and 
> pass that statement to return a ResultSet object?

I do see some sort of way around this though it does strike me as a bit 
of a Kludge.

I can create a dummy class for these queries as every instance will 
return the same columns. This is essentially empty and nothing more than 
a list of column names. This allows a valid class to be loaded.

 From here I can generate arbitrary SQL as I like (as long as the 
columns stay the same) and feed it to the dummy class like so: (Catalyst 
implementation)

    my $items = $c->model('Data::UserDef')->search({},{ from => $sql });

While that is all well and good the kludge is in the SQL statement 
itself that not only requires placement in enclosing brackets but 
requires a construct like '(select * from mytable) as me' given the me 
alias is imposed by the generated SQL but it is not generating the 
trailing alias. 

Bug? || Fractured example of usage above that just happens to work?

Any suggestions?

Neil

>
> Alternately the question is Can this be mapped to a data structure 
> that can be used in ->search() to return a ResultSet?
>
> Below are the example statements as would generate from 3 iterations.
> There are not 3 queries, there are 'n' queries depending of the depth 
> of iteration that will build in this sequence as shown. That is why 
> there needs to be a runtime generation of the select logic.
>
> /* First Iteration */
>
> select distinct m2.inventorycategoryid, c2.name itemname, c2p.id 
> parentcategoryid, c2p.name parentcategoryname from 
> inventorycategorymap m2
> join inventorycategories c2 on c2.id = m2.inventorycategoryid
> join inventorycategories c2p on c2.parentcategoryid = c2p.id
> where m2.inventoryitemid in (
> select m.inventoryitemid from inventoryitems i
> inner join inventorycategorymap m on m.inventoryitemid = i.id
> where m.inventorycategoryid = 3
> ) and m2.inventorycategoryid <> 3 order by c2p.name, c2.name;
>
> /* Second Iteration */
>
> select * from (
> select distinct m2.inventorycategoryid, c2.name itemname, c2p.id 
> parentcategoryid, c2p.name parentcategoryname from 
> inventorycategorymap m2
> join inventorycategories c2 on c2.id = m2.inventorycategoryid
> join inventorycategories c2p on c2.parentcategoryid = c2p.id
> where m2.inventoryitemid in (
> select m.inventoryitemid from inventoryitems i
> inner join inventorycategorymap m on m.inventoryitemid = i.id
> where m.inventorycategoryid = 3
> ) and m2.inventorycategoryid <> 3 order by c2p.name, c2.name
> ) as tb2 where tb2.parentcategoryid <> 102 or tb2.inventorycategoryid 
> = 105
>
> /* Third Iteration */
>
> select * from (
> select * from (
> select distinct m2.inventorycategoryid, c2.name itemname, c2p.id 
> parentcategoryid, c2p.name parentcategoryname from 
> inventorycategorymap m2
> join inventorycategories c2 on c2.id = m2.inventorycategoryid
> join inventorycategories c2p on c2.parentcategoryid = c2p.id
> where m2.inventoryitemid in (
> select m.inventoryitemid from inventoryitems i
> inner join inventorycategorymap m on m.inventoryitemid = i.id
> where m.inventorycategoryid = 3
> ) and m2.inventorycategoryid <> 3 order by c2p.name, c2.name
> ) as tb2 where tb2.parentcategoryid <> 102 or tb2.inventorycategoryid 
> = 105
> ) as tb3 where tb3.parentcategoryid <> 110 or tb3.inventorycategoryid 
> = 111
>
>