Skip to content

Add extendWhere, orWhere and andWhere methods to database query class.#5837

Closed
chrisdavenport wants to merge 3 commits intojoomla:stagingfrom
chrisdavenport:db-where
Closed

Add extendWhere, orWhere and andWhere methods to database query class.#5837
chrisdavenport wants to merge 3 commits intojoomla:stagingfrom
chrisdavenport:db-where

Conversation

@chrisdavenport
Copy link
Copy Markdown
Contributor

@chrisdavenport chrisdavenport commented Jan 20, 2015

The current implementation of the database query where method is limited by an unfortunate restriction in that the first use of the glue parameter fixes the glue for all subsequent uses. For example,

$query->where(array('foo = 1', 'bar = 2'));
// WHERE foo = 1 AND bar = 2

$query->where(array('foo = 1', 'bar = 2'), 'OR');
// WHERE foo = 1 OR bar = 2

$query
->where(array('foo = 1', 'bar = 2'), 'AND')
->where(array('baz = 3', 'goo = 4'), 'OR');
// WHERE foo = 1 AND bar = 2 AND baz = 3 AND goo = 4
// Note that the OR glue is completely ignored in the second where statement.

This behaviour is inconvenient but can't be changed in Joomla 3.x because it would break backwards compatibility. Consequently, this PR offers the following additional syntax to support more complex WHERE clauses:

orWhere($conditions, $glue = 'AND')

$query
->where(array('foo = 1', 'bar = 2'))
->orWhere(array('baz = 3', 'goo = 4'));
// WHERE (foo = 1 AND bar = 2) OR (baz = 3 AND goo = 4)

$query
->where(array('foo = 1', 'bar = 2'))
->orWhere(array('baz = 3', 'goo = 4'), 'XOR');
// WHERE (foo = 1 AND bar = 2) OR (baz = 3 XOR goo = 4)

andWhere($conditions, $glue = 'OR')

$query
->where(array('foo = 1', 'bar = 2'))
->andWhere(array('baz = 3', 'goo = 4'));
// WHERE (foo = 1 AND bar = 2) AND (baz = 3 OR goo = 4)

$query
->where(array('foo = 1', 'bar = 2'))
->andWhere(array('baz = 3', 'goo = 4'), 'XOR');
// WHERE (foo = 1 AND bar = 2) AND (baz = 3 XOR goo = 4)

extendWhere($outerGlue, $conditions, $innerGlue = 'AND')

$query
->where(array('foo = 1', 'bar = 2'))
->extendWhere('XOR', array('baz = 3', 'goo = 4'));
// WHERE (foo = 1 AND bar = 2) XOR (baz = 3 AND goo = 4)

$query
->where(array('foo = 1', 'bar = 2'))
->extendWhere('XOR', array('baz = 3', 'goo = 4'), 'OR');
// WHERE (foo = 1 AND bar = 2) XOR (baz = 3 OR goo = 4)

The extendWhere method allows additional logical operators to be used beyond the commonly used AND and OR operators. For example, you can use XOR, AND NOT and OR NOT.

Backwards compatibility
There are no backwards compatibility issues. Existing code is unaffected because only new syntax is being added.

Testing
The code is accompanied by unit tests for the new methods and an extension to the where method test which verifies the original behaviour.

@jackkum
Copy link
Copy Markdown
Contributor

jackkum commented Jan 20, 2015

awesome, i waiting this 😄
#5601

@compojoom
Copy link
Copy Markdown
Contributor

compojoom commented Mar 14, 2015

I've tested this and it works.
However with great power comes great responsibility :p

With the new syntax one has to be really careful in what order the andWhere and orWhere parameters are added. If you change their position something completely different than expected would come out.
The benefit of the query class was that you could add everything in a mixed order, now with this, we have to to be really careful how we build the where part of the query. I'm not sure if this is a drawback or not...

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/5837.

@chrisdavenport
Copy link
Copy Markdown
Contributor Author

chrisdavenport commented Mar 14, 2015

Given an arbitrary query object with some unknown WHERE clause, adding an andWhere or an orWhere to it would, I think, give exactly the result you would expect. If you were to add a where([something], 'and') or a where([something], 'or') you would very likely get something unexpected since the second argument would be ignored, so I would say that the behaviour of the existing methods is rather more unexpected.

Can you give me an example of something unexpected coming out when using andWhere or orWhere?

@photodude
Copy link
Copy Markdown
Contributor

photodude commented Dec 15, 2015

💯 Would be nice to see this maybe added to 3.5

@chrisdavenport
Copy link
Copy Markdown
Contributor Author

chrisdavenport commented Dec 15, 2015

Then please test it and report your findings. Without testers it cannot be committed. ;-)

@photodude
Copy link
Copy Markdown
Contributor

photodude commented Dec 16, 2015

I attempted to test the patch but I get an error.
"Error: The patch could not be applied because the repository is missing"

I'm guessing that this error is due to "This branch has conflicts that must be resolved" or some other issue with the patch tester.

@mbabker
Copy link
Copy Markdown
Contributor

mbabker commented Dec 16, 2015

Nope, it's right. It looks like the source branch for this pull request was deleted.

@chrisdavenport
Copy link
Copy Markdown
Contributor Author

chrisdavenport commented Dec 16, 2015

Git got its knickers in a twist a while back and the only solution was to delete my repo and start again with a clean fork. I'll create a new PR.

@chrisdavenport chrisdavenport mentioned this pull request Dec 16, 2015
Closed
@chrisdavenport
Copy link
Copy Markdown
Contributor Author

chrisdavenport commented Dec 16, 2015

Closing in favour of #8718

@photodude photodude mentioned this pull request Dec 22, 2015
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

Feature Unit/System Tests

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

8 participants

@chrisdavenport @jackkum @compojoom @photodude @mbabker @wilsonge @jissues-bot @joomla-cms-bot