Select all fields where fieldone is not equal to null + Propel
Asked Answered
D

4

9

I have a question about using the propel ORM and creating a query.

I have a table "locations" with fields:

  • location
  • sublocation
  • postcode
  • street
  • number

Now I want to select all the locations where the location field IS NOT equal to 'null'.
How can I do this? I've tried this but I get back all the results ...

Tried query: $locations = LocationQuery::create()->where('location' != null)->find();

Donica answered 24/8, 2013 at 10:13 Comment(0)
K
5

I don't know propel. But the proper SQL syntax for the expression would be:

$locations = LocationQuery::create()->where('location is not null')->find();

Any comparison to NULL in SQL returns NULL, which is treated as false. With the exception of is null and is not null.

Kironde answered 24/8, 2013 at 11:54 Comment(1)
Thanks! The right syntax was: locations = LocationQuery::create()->where('location IS NOT NULL')->find();Donica
A
24

You can use this:

->filterByColumnName(null, Criteria::NOT_EQUAL) 

There are various 'Criteria' uses in propel, listed here: propel criteria

There isn't an exact sample for this on the site, the closest is this:

->filterByTags(array('novel', 'russian'), Criteria::CONTAINS_NONE)
Allusive answered 17/1, 2014 at 13:11 Comment(1)
This is the 'Correct Propel' wayMove
B
7

You can also use

->filterByColumnName(null, CRITERIA::ISNOTNULL)
Briarroot answered 24/6, 2015 at 10:57 Comment(0)
K
5

I don't know propel. But the proper SQL syntax for the expression would be:

$locations = LocationQuery::create()->where('location is not null')->find();

Any comparison to NULL in SQL returns NULL, which is treated as false. With the exception of is null and is not null.

Kironde answered 24/8, 2013 at 11:54 Comment(1)
Thanks! The right syntax was: locations = LocationQuery::create()->where('location IS NOT NULL')->find();Donica
B
1

You can reference all of the Propel 2 comparison types for CRITERIA::_needed_type_ here.

EQUAL
NOT_EQUAL
ALT_NOT_EQUAL
GREATER_THAN
LESS_THAN
GREATER_EQUAL
LESS_EQUAL
LIKE
NOT_LIKE
CONTAINS_ALL
CONTAINS_SOME
CONTAINS_NONE
ILIKE
NOT_ILIKE
CUSTOM
RAW
CUSTOM_EQUAL
DISTINCT
IN
NOT_IN
ALL
JOIN
BINARY_AND
BINARY_OR
ASC
DESC
ISNULL
ISNOTNULL
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
LEFT_JOIN
RIGHT_JOIN
INNER_JOIN
LOGICAL_OR
LOGICAL_AND
Butyrin answered 16/7, 2015 at 14:19 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.