[Dbix-class] Limitations in building WHERE-Clauses?

[Sven Eppler]

Oh well...i didn't knew this trick with the scalar reference. This is
pretty cool. Thanks alot! :)

On Tue, June 12, 2007 09:50, Peter Edwards wrote:
> Something like this should work
>
> for ( $schema->resultset('Table')->search({
>         field2 => \"> DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)",
>       })->all )
> {
>   print $_->get_column('field1'), "\n";
> }
>
> Here's a real world example from a SQLite database
>
> # get first live playlist record in descending priority order
> my $rsPlaylist = $this->{schema}->resultset('Playlist')->search({
>     device_region_id => $region_id,
>     status => [ 'ready', 'playing' ],
>     start_dt => \"<= DATETIME('NOW','localtime','+1 seconds')",
>     end_dt => \"> DATETIME('NOW','localtime')",
>   },
>   {
>     order_by => 'priority DESC',
>   })->first;
>
>
> Documentation:
>
> http://search.cpan.org/~blblack/DBIx-Class-0.07006/lib/DBIx/Class/ResultSet.
> pm#search
>
> http://search.cpan.org/~blblack/DBIx-Class-0.07006/lib/DBIx/Class/Manual/Coo
> kbook.pod#Searching
>
>
> Regards, Peter
> Dragonstaff Limited  http://www.dragonstaff.com   Business IT Consultancy
>
> -----Original Message-----
> From: Sven Eppler [mailto:sven at sveneppler.de]
> Sent: 12 June 2007 08:08
> To: dbix-class at lists.rawmode.org
> Subject: [Dbix-class] Limitations in building WHERE-Clauses?
>
> Hello!
>
> I'm realy a big fan of DBIx::Class. It makes life so much easier.
>
> But slowly it seems to me, that it will mak life easier only in the first
> look. My special problem looks in SQL just as simple as this:
>
> --- SQL ---
> SELECT field1 FROM table
> WHERE DATE(field2) > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
> --- /SQL ---
>
> Well, as you see, it's a quite easy (My)SQL-Statement to retrieve
> everything thats one week old. I know, i could workaround the problem by
> not defining field2 as a Datetime-Field but have a separated DATE and a
> TIME field. But actually suits the DATETIME field the problem much better.
> So if i wanted to compare the Datetime-Field against a date i need to
> "cast" the Datetime-Field to a date field with DATE().
>
> But currently i don't see any possibility to accomplish this in
> DBIx::Class? Or am i just overlooking something?
>
> The other thing ist the right side of the WHERE-Clause. The desired
> function calls would probably be recreateable in DBIx::Class. But i hope
> you all agree with me that even in this simple case (no ANDin or ORing)
> the Syntax-Overhead is really going somewhere i don't want it do go.
>
> So i asked for myself if there is a way i can run "raw SQL" through
> DBIx::Class or can't i benefit in any way from DBIx::Class in such
> situations and have to get me myself a DB-Handle from DBI and run my SQL
> the way it was done in the old days?
>
> Thanks in advance,
> Sven
>
>
>
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
>