Catalyst - SQL query

[megaurav2002]

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

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

[fmerges]

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,

[eriam]

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

[one4k4]

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

...get that $var.

select *
from tbl_loan
where RecordTime = ?

...

$db->execute($var);

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.

[megaurav2002]

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

[one4k4]

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. :)