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

[Peter Edwards]

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/