Catalyst - SQL query

megaurav2002 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, I have a SQL query which works fine on MYSQL database. But i cant convert it to run it in catalyst controller. The query is :

SELECT *
from tbl_loan
WHERE RecordTime IN (select Max(RecordTime)
from tbl_loan
WHERE UserId = 2
Group By LoanId);
[download]

Can anyone please tell me how to write the same query in controller in Catalyst. Thanks very much.

Comment on Catalyst - SQL query Download Code

Replies are listed 'Best First'.
Re: Catalyst - SQL query by fmerges (Chaplain) on May 10, 2007 at 07:54 UTC
Hi, Subselects doesn't work yet with DBIx::Class. But I think there was a way of doing it with -in option and passing the inner select directly. Regards, fmerges at irc.freenode.net	[reply]
Re: Catalyst - SQL query by eriam (Beadle) on May 10, 2007 at 08:25 UTC
Hello There is an interesting thread on the Catalyst mailing list actually that discuss how to use controllers and models and what are the best practices with Catalyst That leads me to suggest that the controller is not the most appropriate place to store the code you are talking about. So now coming back to the code itself, on the sql side I think a join on the same table (with aliases) would be more appropriate ... so that leads as fmerges suggested to implementing joins in DBIC which is supported. Good luck. Eriam	[reply]
Re: Catalyst - SQL query by one4k4 (Hermit) on May 10, 2007 at 12:10 UTC
As fmerges mentioned above, subselects may not work yet. What about something similar to a multi-step query then? `select max(RecordTime) from tbl_loan where UserId = 2 Group By LoanId` [download] ...get that $var. `select * from tbl_loan where RecordTime = ? ... $db->execute($var);` [download] Just a few cents, may or may not help. Just offering the idea that maybe you have to workaround it and take it as two steps.	[reply] [d/l] [select]
Re^2: Catalyst - SQL query by megaurav2002 (Monk) on May 11, 2007 at 00:45 UTC
Thanks ! Your answer makes sense to me. It can work for me with little modification. Can you please tell me which module does `$db->execute($var)` belongs to? Regards	[reply] [d/l]
Re^3: Catalyst - SQL query by one4k4 (Hermit) on May 11, 2007 at 13:44 UTC
Oh, I apologize. $db->execute was a reference to your database handle created via the DBI module. But what I was really trying to mention was the idea of using two separate queries. :)	[reply]