[Dbix-class] Complex Query to generate at runtime

[Neil Lunn]

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?

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


-- 

Regards,

*Neil Lunn*
**