Database Where Clauses



The ZN Framework supports a number of different use cases. There are many methods you can use as an alternative to using standard DB::where().

 

 

# Methods


this whereAnd ( String $ column , String $ value )
this whereOr ( String $ column , String $ value )
this whereNot ( String $ column , String $ value , String $ logical = ' AND ' )
this whereLike ( String $ column , String $ value , String $ logical = ' AND ' )
this whereStartLike ( String $ column , String $ value , String $ logical = ' AND ' )
this whereEndLike ( String $ column , String $ value , String $ logical = ' AND ' )
this whereBetween ( String $ column , String $ value1 , String $ value2 , String $ logical = ' AND ' )
this whereIn ( String $ column , Array $ values , String $ logical = ' AND ' )
this whereNotIn ( String $ column , Array $ values , String $ logical = ' AND ' )
this whereInTable ( String $ column , String $ value , String $ logical = ' AND ' )
this whereInQuery ( String $ column , String $ value , String $ logical = ' AND ' )
this whereJson ( String $ column , String $ value = NULL , String $ logical = ' AND ' )
this whereNotJson ( String $ column , String $ value = NULL , String $ logical = ' AND ' )
this whereColumnnameCondition ( Scalar $ data )

 

 

# Example Table


According to a sample data table as below, we see which methods and results we will obtain.

blogs
ID title content
1 hello {"tr": "Hello", "en": "Hello"}
2 goodbye {"tr": "Güle güle", "en": "Goodbye"}
3 the howareyo {"tr": "How are you?", "en": "How are you?"}

 

 

# WhereAnd ( ZN & gt; = 5.6.3 )


Connects conditions with AND linker.

Parameters

String $ Columnname Column name.
String $ Value Column value.
return this

uses

$blogs = DB::whereAnd('id', 2)->where('title', 'goodbye')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '2' (length = 3 )
        title => string 'goodbye' (length = 9 )
        content => string ' "} ' (length = 38 )
SELECT * FROM blogs WHERE id = '2' AND title = 'goodbye' 

 

 

# WhereOr ( ZN & gt; = 5.6.3 )


Connects conditions with OR linker.

Parameters

String $ Columnname Column name.
String $ Value Column value.
return this

uses

$blogs = DB::whereOr('id', 2)->where('id', 2)->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '1' (length = 3 )
        title => string 'hello' (length = 7 )
        content => string "{" t ":" Hello "," en "," Hello " } (length = 32 )

1 => object 

        id => string '2' (length = 3 )
        title = string 'goodbye'(Length = 9 )
        content => string "{" en "," Goodbye "," en "," Goodbye "} (length = 38 )
SELECT * FROM blogs WHERE id = '1' OR id = '2' 

 

 

# WhereNot ( ZN & gt; = 5.6.3 )


Compares the column by not equal (! =).

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereNot('id', 1)->whereNot('id', 2)->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '3' (length = 3 )
        title => string 'howareyou' (length = 11 )
        content => string ' are you? "} ' (length = 41 )
SELECT * FROM blogs WHERE id! = '1' and id! = '2'

 

 

# WhereLike ( ZN & gt; = 5.6.3 )


Used to search for colons in% value%.

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereLike('title', 'bye')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '2' (length = 3 )
        title => string 'goodbye' (length = 9 )
        content => string ' "} ' (length = 38 )
SELECT * FROM blogs WHERE title like '% bye%' 

 

 

# WhereStartLike ( ZN & gt; = 5.6.3 )


Used to search for the value of the column starting with (% value).

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereStartLike('title', 'how')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '3' (length = 3 )
        title => string 'howareyou' (length = 11 )
        content => string ' are you? "} ' (length = 41 )
SELECT * FROM blogs WHERE title like 'how%' 

 

 

# WhereEndLike ( ZN & gt; = 5.6.3 )


Used to search for colum (% value) with colum.

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereEndLike('title', 'you')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '3' (length = 3 )
        title => string 'howareyou' (length = 11 )
        content => string ' are you? "} ' (length = 41 )
SELECT * FROM blogs WHERE title like '% you' 

 

 

# WhereBetween ( ZN & gt; = 5.6.3 )


Used to supply data between and including specified values. BETWEEN value1 AND value2 is the counterpart of the SQL syntax.

Parameters

String $ Columnname Column name.
String $ value1 Starting value
String $ value2 End value.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereBetween('id', 1, 2)->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '1' (length = 3 )
        title => string 'hello' (length = 7 )
        content => string "{" t ":" Hello "," en "," Hello " } (length = 32 )

1 => object 

        id => string '2' (length = 3 )
        title = string 'goodbye'(Length = 9 )
        content => string "{" en "," Goodbye "," en "," Goodbye "} (length = 38 )
SELECT * FROM blogs WHERE id between 1 AND

 

 

# WhereIn ( ZN & gt; = 5.6.3 )


Checks whether the column is between the specified values. Between the results brings.

Parameters

String $ Columnname Column name.
Array $ values They are worthy of being in the colony.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereIn('id', [3, 4, 7])->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '3' (length = 3 )
        title => string 'howareyou' (length = 11 )
        content => string ' are you? "} ' (length = 41 )
)
SELECT * FROM blogs WHERE id in (3, 4, 7

 

 

# WhereNotIn ( ZN & gt; = 5.6.3 )


Checks whether the column is between the specified values. If it is not in between, it will bring the results.

Parameters

String $ Columnname Column name.
Array $ values They are worthy of being in the colony.
String $ logical = ' AND ' conjunction
return this

uses

$blogs = DB::whereNotIn('id', [1, 3, 4])->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '2' (length = 3 )
        title => string 'goodbye' (length = 9 )
        content => string ' "} ' (length = 38 )
SELECT * FROM blogs WHERE id not in (1, 3, 4

 

 

# WhereJson ( ZN> = 5.6.3 & MySQL> = 5.7 )


As of the versions mentioned above, JSON is used to fetch the results according to the matching value from the column holding the data.

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = NULL Conjunctions.
Options AND , OR
return this

uses

$blogs = DB::whereJson('content', 'Goodbye')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '2' (length = 3 )
        title => string 'goodbye' (length = 9 )
        content => string ' "} ' (length = 38 )
SELECT * FROM blogs WHERE JSON_SEARCH (content, 'one' , 'Goodbye' ) IS NOT NULL 

 

 

# WhereNotJson ( ZN> = 5.6.3 & MySQL> = 5.7 )


As of the versions mentioned above, JSON is used to fetch the results according to the non-matching value in the column holding the data .

Parameters

String $ Columnname Column name.
String $ Value Column value.
String $ logical = NULL Conjunctions.
Options AND , OR
return this

uses

$blogs = DB::whereNotJson('content', 'Goodbye')->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '1' (length = 3 )
        title => string 'hello' (length = 7 )
        content => string "{" t ":" Hello "," en "," Hello " } (length = 32 )

1 => object 

        id => string '3' (length = 3 )
        title = string 'howareyou'(Length = 11 )
        content => string '{ "en": "How are you?", "Most": "How are you?"}' (Length = 41 )
SELECT * FROM blogs WHERE JSON_SEARCH (content, 'one' , 'Goodbye' ) IS NULL 

 

 

# WhereColumnnameCondition ( ZN> = 5.2.0 )


This special WHERE use is designed to create the desired WHERE structure without using extra parameters according to the desired column name and connector type . The method name is used to specify the desired column and any other WHERE structure to be joined, specifying the name of the connector.

Parameters

scaler $ Value Column value.
return this  

Use of

$blogs = DB::whereTitleOr('Goodbye')->whereId(1)->blogs();

output($blogs->result());

output($blogs->stringQuery());
0 => object 

        id => string '1' (length = 3 )
        title => string 'hello' (length = 7 )
        content => string "{" t ":" Hello "," en "," Hello " } (length = 32 )

1 => object 

        id => string '2' (length = 3 )
        title = string 'goodbye'(Length = 9 )
        content => string "{" en "," Goodbye "," en "," Goodbye "} (length = 38 )
SELECT * FROM blogs WHERE title = 'Goodbye' Or id = '1'