DBIx-Class

Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

9 messages

Karen Hoofnagle

Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

Some javascript/style in this post has been disabled (why?)

I need to create the following query using DBIx:Class (assume I'm using the most recent release of DBIx::Class)

select * from user_read_log where datetime between to_date(20090101, 'YYYYMMDD') and to_date(20091001, 'YYYYMMDD')

I've read the section of SQL::Abstract dealing with arrayrefs and scalarrefs and I am clearly not getting it. I've screwed around with a bunch of different syntaxes and am missing the mark.

This is just one of my attempts to make this thing fly:

$rs = $c->model('DB::User_Read_Log')->search(

{

datetime =>{"between", \["to_date(?, 'YYYYMMDD HH24:MM:SS')" =>$start. " 00:00:00",

"to_date(?, 'YYYYMMDD HH24:MM:SS')" => $end. " 23:59:59"]

}

);

This particular version returns the error

ERROR: DBIx::Class::ResultSet::all(): DBIx::Class::ResultSet::all(): [SQL::Abstract::_where_field_BETWEEN]

Fatal: special op 'between' requires an arrayref of two values (or a scalarref or arrayrefref for literal SQL) at /home/khoofnag/svn/RCN-TEADWeb/script/../lib/RCN/TEADWeb/Controller/WorkOrder.pm line 93

Suggestions both for solving the problem at hand and for reading to really understand how to use functions in a where clause when coming from DBIx:Class. (Yes, I read the SQL::Abstract cpan page and it doesn't really serve someone dropping in conceptually from DBIx::Class. I'm looking for other discussions.)

Once I properly understand this stuff, I will see what I can do to add this example to the DBIx:Class cookbook and if I can find a way as a n00b to decently articulate what I've not been getting up to now, I'll see about blogging that in as well since it seems fundamental to getting along effectively with the package.

Thanks,

Karen

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Joel Bernstein

Re: Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

2009/9/21 Karen Hoofnagle <[hidden email]>:
> I need to create the following query using DBIx:Class (assume I'm using the
> most recent release of DBIx::Class)
> select * from user_read_log where datetime between to_date(20090101,
> 'YYYYMMDD') and to_date(20091001, 'YYYYMMDD')

I think you said you've looked here, but I guess you didn't see this section:

http://search.cpan.org/~ribasushi/SQL-Abstract-1.58/lib/SQL/Abstract.pm#Special_operators_:_IN,_BETWEEN,_etc.

/joel

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Karen Hoofnagle

Re: Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

No, I saw it. But I appear to be implementing what I'm reading
incorrectly.
Which is why I asked for help.

On Sep 21, 2009, at 12:26 PM, Joel Bernstein wrote:

Will Hawes

Re: Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

In reply to this post by Karen Hoofnagle

2009/9/21 Karen Hoofnagle <[hidden email]>:

> I need to create the following query using DBIx:Class (assume I'm using the
> most recent release of DBIx::Class)
> select * from user_read_log where datetime between to_date(20090101,
> 'YYYYMMDD') and to_date(20091001, 'YYYYMMDD')
> I've read the section of SQL::Abstract dealing with arrayrefs and scalarrefs
> and I am clearly not getting it. I've screwed around with a bunch of
> different syntaxes and am missing the mark.
> This is just one of my attempts to make this thing fly:
>
> $rs = $c->model('DB::User_Read_Log')->search(
>    {
>    datetime =>{"between", \["to_date(?, 'YYYYMMDD HH24:MM:SS')"
> =>$start. " 00:00:00",
>    "to_date(?, 'YYYYMMDD
> HH24:MM:SS')" => $end. " 23:59:59"]
>    }
>
>    }
>         );

IIRC you need to pass in an arrayref containing two scalarrefs as follows:

datetime => { "between", [ \"to_date(...)", \"to_date(...)" ] }

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Karen Hoofnagle

Re: Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

Are you suggesting the code should look like this:

$rs = $c->model('DB::User_Read_Log')->search(
{
datetime =>{"between", [\"to_date(?, 'YYYYMMDD HH24:MM:SS')"
=>$start." 00:00:00", \"to_date(?, 'YYYYMMDD HH24:MM:SS')" => $end. "
00:00:00"] }
}
);

I still get the same error (pasted again below).

ERROR: DBIx::Class::ResultSet::all(): DBIx::Class::ResultSet::all():
[SQL::Abstract::_where_field_BETWEEN] Fatal: special op 'between'
requires an arrayref of two values (or a scalarref or arrayrefref for
literal SQL) at /home/khoofnag/svn/RCN-TEADWeb/script/../lib/RCN/
TEADWeb/Controller/WorkOrder.pm line 93

On Sep 21, 2009, at 12:47 PM, Will Hawes wrote:

> 2009/9/21 Karen Hoofnagle <[hidden email]>:
>> I need to create the following query using DBIx:Class (assume I'm
>> using the
>> most recent release of DBIx::Class)
>> select * from user_read_log where datetime between to_date(20090101,
>> 'YYYYMMDD') and to_date(20091001, 'YYYYMMDD')
>> I've read the section of SQL::Abstract dealing with arrayrefs and
>> scalarrefs
>> and I am clearly not getting it. I've screwed around with a bunch of
>> different syntaxes and am missing the mark.
>> This is just one of my attempts to make this thing fly:
>>
>> $rs = $c->model('DB::User_Read_Log')->search(
>> {
>> datetime =>{"between", \["to_date(?, 'YYYYMMDD HH24:MM:SS')"
>> =>$start. " 00:00:00",
>> "to_date(?,
>> 'YYYYMMDD
>> HH24:MM:SS')" => $end. " 23:59:59"]
>> }
>>
>> }
>> );
>
> IIRC you need to pass in an arrayref containing two scalarrefs as
> follows:
>
> datetime => { "between", [ \"to_date(...)", \"to_date(...)" ] }
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Will Hawes

Re: Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

Reply Threaded

2009/9/21 Karen Hoofnagle <[hidden email]>:

> Are you suggesting the code should look like this:
>
> $rs = $c->model('DB::User_Read_Log')->search(
> {
> datetime =>{"between", [\"to_date(?, 'YYYYMMDD HH24:MM:SS')"
> =>$start." 00:00:00", \"to_date(?, 'YYYYMMDD HH24:MM:SS')" => $end. "
> 00:00:00"] }
> }
> );
>
> I still get the same error (pasted again below).
>
> ERROR: DBIx::Class::ResultSet::all(): DBIx::Class::ResultSet::all():
> [SQL::Abstract::_where_field_BETWEEN] Fatal: special op 'between' requires
> an arrayref of two values (or a scalarref or arrayrefref for literal SQL) at
> /home/khoofnag/svn/RCN-TEADWeb/script/../lib/RCN/TEADWeb/Controller/WorkOrder.pm
> line 93

Ah - I had expected the following to work, but it doesn't:

my $where = {
datetime => {
"between",
[
\[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$start 00:00:00" ],
\[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$end 00:00:00" ]
]
}
};

It seems that while a reference to an arrayref can be used with
"between", elements within said arrayref are currently limited to
either scalars or references to scalars. Maybe a candidate for a
patch?

I think the following does what you want though:

my $where = {
datetime => {
"between",
\[
"to_date(?, 'YYYYMMDD HH24:MM:SS') AND to_date(?,
'YYYYMMDD HH24:MM:SS')",
"$start 00:00:00",
"$end 00:00:00"
]
}
};

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

ANNOUNCE: SQL::Abstract 1.59 (was: Writing a query with a BETWEEN in the WHERE clause and the to_date(function))

Reply Threaded

Will Hawes wrote:

>
> Ah - I had expected the following to work, but it doesn't:
>
> my $where = {
> datetime => {
> "between",
> [
> \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$start 00:00:00" ],
> \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$end 00:00:00" ]
> ]
> }
> };
>
> It seems that while a reference to an arrayref can be used with
> "between", elements within said arrayref are currently limited to
> either scalars or references to scalars. Maybe a candidate for a
> patch?
>

SQL::Abstract 1.59 just released on CPAN should nicely support all
combinations of the above.

Cheers

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: ANNOUNCE: SQL::Abstract 1.60

Reply Threaded

Peter Rabbitson wrote:

> Will Hawes wrote:
>> Ah - I had expected the following to work, but it doesn't:
>>
>> my $where = {
>> datetime => {
>> "between",
>> [
>> \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$start 00:00:00" ],
>> \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$end 00:00:00" ]
>> ]
>> }
>> };
>>
>> It seems that while a reference to an arrayref can be used with
>> "between", elements within said arrayref are currently limited to
>> either scalars or references to scalars. Maybe a candidate for a
>> patch?
>>
>
> SQL::Abstract 1.59 just released on CPAN should nicely support all
> combinations of the above.
>

And SQL::Abstract 1.60 follows suit, as changes to the tester exposed subtle
problems with the tokenizer (no functional changes between 1.59 and 1.60).

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Karen Hoofnagle

Re: ANNOUNCE: SQL::Abstract 1.60

Reply Threaded

As of SQL::Abstract 1.60, the following code now properly supports
using functions with between.

my $rs = $schema->resultset('User_Read_Log')->search(
{
datetime => {
"between",
[
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$start 00:00:00" ],
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$end 00:00:00" ]
]
}

Thanks so much Peter and Will and everyone who responded. I'll see
about propagating this example to the cookbook.
Karen

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...