where clause with AND-glue AND OR-glue in query objects

[hoochicken]

Hi,
I'm facing a problem.
I've got a list of entries of the database.
Thereon I use some filters in the frontend; they work fine using the query->where stuff a .s.o.
They are all "single queries", meaning like "WHERE flat=1" or "WHERE flat=1 AND owner=31".

But there are also filters that are supposed to work as multiple selections, so I need a query like, "WHERE regio_id=123 OR regio_id=34 OR regio_id=32", and combined of course to "WHERE flat=1 AND (regio_id=123 OR regio_id=34 OR regio_id=32)".
I found, that the "glue" can be added; so $query->where($arr_conditions,'OR') should produce the wished result.

Yet it won't work, and as the file libraries/joomla/database/query.php tells me, it can't, for it says in the comment of JDatabaseQueryElement::where(): "Note that the glue is set on first use and cannot be changed." (Life is unfair sometimes...)

I need OR as well as AND statements in my where clause.
Anybody an idea, how to make this work anyway.
I was thinking about three ways, but I don't really like any of them:

• Producing the whole query string manually without J!'s comfortable way of generating a query (*brrr)
  
• Taking the half query already generated by J! und doing the end with the where clause manually (*bäh)
  
• Filtering the items afterwards in the getItems() method of the model 

Maybe it is possible to add a where clause manually without disturbing the query object?

For any hint and idea grateful,

Mareike

PS: Does anybody know the reason for the "Note that the glue is set on first use and cannot be changed."-behaviour?

[Naouak]

I had proposed a solution for that in march but not every one agreed to my solution and so it was not merged.

It was there in case you want to work with it to do a patch : https://github.com/Naouak/joomla-platform/commits/feature/nested-where

Naouak, Grade 2 de Kobal.
Site web: http://www.naouak.net

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/6viM65KSmCEJ.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

[hoochicken]

Hi,
Thank you very much for your answer.
Greetings Mareike
PS: I ended up with a preg_match("/WHERE/", "WHERE (regio=12 OR regio=32) AND ",$query); Hope there will be a proper solution in the core iteself some day :-)

[elin]

I was looking at that suggestion the other day because it seemed like an important improvement. As sometimes happens with the social coding aspect of the github tracker the discussion seemed to go off on a tangent, but that doesn't mean that we should lose the original idea. I think Andrew's suggestion about opening a thread on the platform list is a good one.

That said, I think there is a way to workaround which is to make a subquery and add it.

Elin

[Naouak]

This is not really a workaround as you will get horrible performances for your query if you use sub query instead of a simple where clause.

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/PcdrAdLpWIcJ.

[Dmitry Rekun]

Hi.

This does not work?

$query->where('flat=1');
$query->where('(regio_id=123 OR regio_id=34 OR regio_id=32)');

Dmitry.

[Ove]

This does not work?

$query->where('flat=1');
$query->where('(regio_id=123 OR regio_id=34 OR regio_id=32)');

Hi,
I think i's not that obvious how to code something like this very constructed example

$query->where('flat=1');
if ( $a == $b)
{
    $query->where('(regio_id=123', 'AND');
}
if ( $c == $d)
{
    $query->where('(regio_id=34', 'OR');
}
if ( $e == $f)
{
    $query->where('(regio_id=32', 'OR');
}

PS.
Well, for this constructed example I would probably use   $query->where('(regio_id IN ('. $myregios . ')';
DS.

Ove

[hoochicken]

Hi Dmitry!
That works and that was just what I was looking for.
I knew there had to be a good solution.
Thank you so much.
Brilliant!
@Ove: There are a lot of ANDs in the query already, so this would not have worked in this special case, nevertheless I might need it someday, thanks a lot!
Greetings Mareike

PS: THE one(!) line: $query->where('('.implode(' OR ',$arr_conditions).')');

[Dmitry Rekun]

Hi hoochicken.

Glad that it was helpful. Sometimes the simpliest things does the thing ;)

Dmitry.

[Ove]

Hi hoochicken,

> @Ove: There are a lot of ANDs in the query already, so this would not
> have worked in this special case, nevertheless I might need it
> someday, thanks a lot!
>

My comment with a constructed example was not any kind of solution but
on how confused I was (and am) with the Glue thing. As the glue is used
I guess it's best to accept the AND as "standard divider" and add any OR
in the code. As Dmitry.

As I wrote and If I understand your special case I would use ... IN
($myids) instead of the ORs. I would normaly have to get those ids from
somewhere else and not hardcoded. Perhaps from the component parameters
as comma separated (or json) list. No ANDs to replace there. ;-)

Ove

[elin]

I think one of the basic issues is that when JDatabaseQuery is creating the query there is a good possibility of error if there is a mix of AND and OR and they are not clearly defined, the basic problem being that it's easy to know what glue to use inside one where->() but it's hard to know how to connect a second where-> unless you have an agreed convention such as that there will just be the one kind of glue automatically generated for all wheres (and if you want mixed you have to work that out by writing out the query more explicitly). 

I'd really like the see where() implemented, I think that is tremendously useful for Marieke's example.