1

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

0
2

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.

4
  • its not exactly the query i searched / wanted, but you explained the syntax so its solved, ty – Marco Jan 27 at 5:39
  • I want to correct my answer if it is not correct. Please express what is not yet perfect. – mickmackusa Jan 27 at 5:50
  • ok: the meaning is to get every item that has the published to true and datenow is larger then publish_up (this is ok) AND (publish_down = '0000-00-00 00:00:00 (aka not set)' OR publish_down is larger then datenow) (if publish_down is set). Your actual solution would bring up every item because of the publish_up, right? – Marco Jan 27 at 5:58
  • 1
    I think I understand. I have updated my answer @Marco thanks. – mickmackusa Jan 27 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.