DBIx-Class

Constructing "WHERE" clause in DBIx

6 messages

Eric Wee

Constructing "WHERE" clause in DBIx

Reply Threaded

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

Hi there,

I’m trying to construct the following “where” clause in DBIx but not sure how to do so.

WHERE

is_retail = '1'

AND enabled_date is not null

AND enabled_date < now()

AND (disabled_date is null

OR disabled_date < enabled_date

OR disabled_date > now())

Any help would be much appreciated.

Best regards,

Eric

______________________________________________________
CONFIDENTIALITY NOTICE
This electronic mail message, including any and/or all attachments, is for the sole use of the intended recipient(s), and may contain confidential and/or privileged information, pertaining to business conducted under the direction and supervision of the sending organization. All electronic mail messages, which may have been established as expressed views and/or opinions (stated either within the electronic mail message or any of its attachments), are left to the sole responsibility of that of the sender, and are not necessarily attributed to the sending organization. Unauthorized interception, review, use, disclosure or distribution of any such information contained within this electronic mail message and/or its attachment(s), is (are) strictly prohibited. If you are not the intended recipient, please contact the sender by replying to this electronic mail message, along with the destruction all copies of the original electronic mail message (along with any attachments).
______________________________________________________

_______________________________________________
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: Constructing "WHERE" clause in DBIx

Reply Threaded

Eric Wee wrote:

> Hi there,
>
>
>
> I’m trying to construct the following “where” clause in DBIx but not
> sure how to do so.
>
>
>
> WHERE
>
> is_retail = '1'
>
> AND enabled_date is not null
>
> AND enabled_date < now()
>
> AND (disabled_date is null
>
> OR disabled_date < enabled_date
>
> OR disabled_date > now())
>
>
>

Show us what you've tried so far.

_______________________________________________
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@...

Eric Wee

RE: Constructing "WHERE" clause in DBIx

Reply Threaded

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

Hi Peter,

Thanks for your prompt reply.

This is as far as I got ..

$c->model(DB::Client)->search(

{

is_retail => '1',

enabled_date => { '!=' => undef },

enabled_date => { '<' => 'NOW' },

};

Not sure how to construct it with ..

> AND (disabled_date is null

> OR disabled_date < enabled_date

> OR disabled_date > now())

Thanks,

Eric

-----Original Message-----
From: Peter Rabbitson [mailto:[hidden email]]
Sent: Monday, 10 August 2009 5:48 PM
To: Class user and developer list
Subject: Re: [Dbix-class] Constructing "WHERE" clause in DBIx

Eric Wee wrote:

> Hi there,

> I’m trying to construct the following “where” clause in DBIx but not

> sure how to do so.

> WHERE

> is_retail = '1'

> AND enabled_date is not null

> AND enabled_date < now()

> AND (disabled_date is null

> OR disabled_date < enabled_date

> OR disabled_date > now())

Show us what you've tried so far.

_______________________________________________

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://[hidden email]

Rob Kinyon

Re: Constructing "WHERE" clause in DBIx

Reply Threaded

On Mon, Aug 10, 2009 at 19:42, Eric Wee<[hidden email]> wrote:

> $c->model(DB::Client)->search(
>       {
>             is_retail => '1',
>             enabled_date => { '!=' => undef },
>             enabled_date => { '<' => 'NOW' },
>       };
>
> Not sure how to construct it with ..
>
>>             AND (disabled_date is null
>>                         OR disabled_date < enabled_date
>>                         OR disabled_date > now())

This should do the trick. Peter will chime in with corrections as needed.

->search({
is_retail => '1',
enabled_date => { '!=' => undef },
enabled_date => { '<' => \'NOW()' },
-and => {
-or => {
disabled_date => undef,
disabled_date => { '<' => 'enabled_date' },
disabled_date => { '>' => \'NOW()', }
},
},
});

The scalarref is important for NOW() otherwise it won't work.

Rob

_______________________________________________
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@...

fREW Schmidt

Re: Constructing "WHERE" clause in DBIx

Reply Threaded

Yeah, that's gonna need to be

-or => [ ... ]

And I don't think the -and is needed.

On Aug 10, 2009 8:58 PM, "Rob Kinyon" <[hidden email]> wrote:

On Mon, Aug 10, 2009 at 19:42, Eric Wee<[hidden email]> wrote: > $c->model(DB::Client)->searc...
This should do the trick. Peter will chime in with corrections as needed.

->search({ is_retail => '1', enabled_date => { '!=' => undef },
enabled_date => { '<' => \'NOW()' },
-and => {
-or => {
disabled_date => undef,
disabled_date => { '<' => 'enabled_date' },
disabled_date => { '>' => \'NOW()', }
},
},
});

The scalarref is important for NOW() otherwise it won't work.

Rob

_______________________________________________
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@...

_______________________________________________
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@...

Eric Wee

RE: Constructing "WHERE" clause in DBIx

Reply Threaded

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

All good guys!

Thanks heaps for your help.

From: fREW Schmidt [mailto:[hidden email]]
Sent: Tuesday, 11 August 2009 1:50 PM
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] Constructing "WHERE" clause in DBIx

Yeah, that's gonna need to be

-or => [ ... ]

And I don't think the -and is needed.

On Aug 10, 2009 8:58 PM, "Rob Kinyon" <[hidden email]> wrote:

On Mon, Aug 10, 2009 at 19:42, Eric Wee<[hidden email]> wrote: > $c->model(DB::Client)->searc...

This should do the trick. Peter will chime in with corrections as needed.

->search({ is_retail => '1', enabled_date => { '!=' => undef },

enabled_date => { '<' => \'NOW()' },
-and => {
-or => {
disabled_date => undef,
disabled_date => { '<' => 'enabled_date' },
disabled_date => { '>' => \'NOW()', }
},
},
});

The scalarref is important for NOW() otherwise it won't work.

Rob

_______________________________________________
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@...