[Dbix-class] Chained resultsets and WHERE operators

[Ronald J Kimball]

On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham <catalyst at mikeraynham.co.uk>wr=
ote:

>
> I have created a query which determines if a given date range exists with=
in
> date ranges stored in the database:
>
> ###
>
> # SELECT * FROM my_table WHERE
> # (start_date <=3D $start_date AND end_date >=3D $start_date) OR
> # (start_date <=3D $end_date   AND end_date >=3D $end_date)
>
> my $date_ranges =3D $rs->search({
>     -or =3D> [
>         -and =3D> [
>             start_date =3D> { '<=3D', $start_date },
>             end_date   =3D> { '>=3D', $start_date },
>         ],
>         -and =3D> [
>             start_date =3D> { '<=3D', $end_date },
>             end_date   =3D> { '>=3D', $end_date },
>         ],
>     ],
> });
>


> Is there a way that I can cause the two queries to be chained together wi=
th
> an OR instead of an AND?
>
> At the moment, I have got round the problem by using the non-chained query
> at the top of this post, and specifying out-of-range defaults for dates t=
hat
> have not been supplied.
>

I don't think there is a way to merge search conditions with an OR when
chaining resultsets.  I would suggest doing something like this:

my $date_ranges =3D $rs->search({
    -or =3D> [
        $start_date
          ? (-and =3D> [
                start_date =3D> { '<=3D', $start_date },
                end_date   =3D> { '>=3D', $start_date },
             ])
          : (),
        $end_date
          ? (-and =3D> [
               start_date =3D> { '<=3D', $end_date },
               end_date   =3D> { '>=3D', $end_date },
             ])
          : (),
    ],
});


Ronald
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101109/5c5=
d7b1e/attachment.htm