Joomla Select from DB AND / OR grouping

Question

I did not found any guide on how to handle such queries in Joomla:

I would like to select all items from db which are:

Published
AND
(the publish up date is 0000-00-00 00:00:00 or smaller then datenow)
OR
(the publish down date is 0000-00-00 00:00:00 or larger then datenow)

to get all items that are published and datenow is within publish start / end date or when setted start/end values are not set.

What I have so far is a simple query:

$query->where($db->quoteName('published') . ' = 1 ');
$query->where($db->quoteName('publish_up') . ' = ' . $db->quote('0000-00-00 00:00:00'))
    ->orWhere($db->quoteName('publish_up') . '<' . $db->quote($date));
$query->where($db->quoteName('publish_down') . ' = ' . $db->quote('0000-00-00 00:00:00'))
    ->orWhere($db->quoteName('publish_down') . '>' . $db->quote($date));
        

So far so good but in the end it builds up a query like so:

SELECT *
FROM `tblname`
WHERE 
(
(`published` = 1  AND `publish_up` = \'0000-00-00 00:00:00\') OR 
(`publish_up`<\'2021-01-26 13:03:07\') OR `publish_down` = \'0000-00-00 00:00:00\') OR 
(`publish_down`>\'2021-01-26 13:03:07\')
ORDER BY ordering ASC

Joomla does not set or group the query as i need it, the first is AND and then everything else is OR.

I'm sure its just a knowledge / syntax gap rather then a bug - but how is the correct way to write a proper query based on my needs?

PS: DB table is from a custom component

Answer 1

Because 0000-00-00 00:00:00 will always be less than the current datetime stamp, I am understanding your requirements as:

published = 1
AND
publish_up <= now
AND
(
    publish_down = 0000-00-00 00:00:00
    OR
    publish_down > now
)

• where() has a default glue value of AND.
• andWhere() has a default glue value of OR.

To translate that to Joomla's querybuilder you would write:

$query
    ->where(
        [
            $db->quoteName('published') . ' = 1'),
            $db->quoteName('publish_up') . ' <= ' . $db->quote($date),
        ]
    )
    ->andWhere(
        [
            $db->quoteName('publish_down') . ' = ' . $db->quote('0000-00-00 00:00:00'),
            $db->quoteName('publish_down') . ' > ' . $db->quote($date)
        ]
    )

Your required logic perfectly aligns with the methods' default glues, so no glue declarations are necessary.

Rendered SQL:

WHERE
(`published` = 1 AND `publish_up` <= '2021-01-26 13:03:07') AND
(`publish_down` = '0000-00-00 00:00:00' OR `publish_down` > '2021-01-26 13:03:07')

Notes:

1. Chaining or not chaining the method calls does not impact the way that the sql is generated. In other words, you don't gain or lose any parentheses from continuing or breaking a "chain" of method calls.

2. I would be using CURRENT_TIMESTAMP unless your $date is not what I think it is. Like:

   $db->quoteName('publish_up') . ' <= CURRENT_TIMESTAMP',
   

3. Here is another of my posts that delves into different combinations of where building syntax.