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

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

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
• the publish up date is 0000-00-00 00:00:00 or smaller then datenow
• the publish down date is 0000-00-00 00:00:00 or larger then datenow

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

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