[Dbix-class] Help with Arbitrary SQL through a custom ResultSource

Tobias Kremer list at funkreich.de
Tue Mar 11 08:25:26 GMT 2008


Quoting Steve Kirkup <skirkup at jupiterimages.com>:
> I guess, I don't understand what the subquery is supposed to encompass.
> My SQL is basically
> SELECT * FROM Table INNER JOIN Sub_Table ON ( Table.id = Sub_Table.id )
> WHERE Table.id = '1'
> Does this mean I should drop the 'SELECT * FROM ' from the SQL in order
> to work?

The arbitrary SQL approach uses the SQL you want to execute as a sub-select
query. This way the normal column selecting, row limiting, paging and
other stuff still works. Consider this query:

SELECT me.* FROM (SELECT * FROM table) AS me;

Adding "rows" and "page" options to your search still works the same way
it would with a regular search - just by adding a LIMIT clause to the end:

SELECT me.* FROM (SELECT * FROM table) AS me LIMIT 15,20;

There might be other reasons to why its done the way it is :)

--Tobias



More information about the DBIx-Class mailing list