Download the PHP package webrium/foxql without Composer
On this page you can find all versions of the php package webrium/foxql. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download webrium/foxql
More information about webrium/foxql
Files in webrium/foxql
Informations about the package foxql
FoxDB query builder similar to Laravel
Attributes:
- ✔️ Low use of resources
- ✔️ Lighter and faster
- ✔️ Similar to Laravel query builder syntax
- ✔️ Easy to configure and use
The Foxdb query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings passed to the query builder as query bindings.
Install by composer
- Configuration
- Select
- Retrieving All Rows
- Retrieving A Single Row
- Retrieving A List Of Column Values
- Chunking Results
- Paginate
- Aggregates
- Select Statements
- Raw Expressions
- Raw Methods
- Join
- Where Clauses
- Ordering
- Latest & oldest Methods
- Random Ordering
- Grouping
- Limit & Offset
- Insert Statements
- Update Statements
- Increment & Decrement
-
Delete Statements
- Special features
- is / true / false and more ...
- Copy method
- Schema
- Eloquent
Add Connection Config
The
'main'
statement is the default name of the connection config
Retrieving All Rows From A Table
You may use the table
method provided by the DB
facade to begin a query. The table
method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:
Retrieving A Single Row / Column From A Table
If you just need to retrieve a single row from a database table, you may use the DB facade's first
method. This method will return a single stdClass object:
If you don't need an entire row, you may extract a single value from a record using the value
method. This method will return the value of the column directly:
To retrieve a single row by its id
column value, use the find
method:
The difference between the find
method and first
is that the first
method returns the result in the form of a stdClass if it exists, but the find
method returns the result in the form of a Model
, which provides us with more features. (If the value does not exist Both methods return false.)
🆕 From version 3 and above, queries can be used for find
Retrieving A List Of Column Values
you may use the pluck
method. In this example, we'll retrieve a collection of user titles:
You may specify the column that the resulting collection should use as its keys by providing a second argument to the pluck method:
Chunking Results
If you need to work with thousands of database records, consider using the chunk
method provided by the DB facade. This method retrieves a small chunk of results at a time and feeds each chunk into a closure for processing. For example, let's retrieve the entire users table in chunks of 100 records at a time:
You may stop further chunks from being processed by returning false from the closure:
you may use the each
method.
Paginate
FoxDB has created a simple method for pagination. In the example below, the number of results is limited to 10 records, and you can get the information by changing the page number.
Its output is a stdClass containing the following properties:
Aggregates
The query builder also provides a variety of methods for retrieving aggregate values like count
, max
, min
, avg
, and sum
. You may call any of these methods after constructing your query:
Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:
Determining If Records Exist
Instead of using the count method to determine if any records exist that match your query's constraints, you may use the exists and doesntExist methods:
Select Statements
Specifying A Select Clause
You may not always want to select all columns from a database table. Using the select
method, you can specify a custom "select" clause for the query:
But there is a more modern way to do this. You can act like the example below
Raw Expressions
Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the raw
method provided by the DB
facade:
To use the parameter in raw like the example below
DB::raw('count(?)',['id'])
⚠️ Raw statements will be injected into the query as strings, so you should be extremely careful to avoid creating SQL injection vulnerabilities.
Our suggestion
But for this purpose, it is better to use the following method to avoid SQL injection
attack
In this structure, you have access to field
, count
, sum
, avg
, min
, max
, all
, as
methods.
Raw Methods
Instead of using the DB::raw method, you may also use the following methods to insert a raw expression into various parts of your query. Remember, Foxdb can not guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.
whereRaw / orWhereRaw
The whereRaw and orWhereRaw methods can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:
havingRaw / orHavingRaw
The havingRaw and orHavingRaw methods may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:
Inner Join Clause
The query builder may also be used to add join clauses to your queries. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:
In Foxdb, you can do it more easily
In this structure, you enter the name of the table you want to join with its foreign key ('orders.user_id'
) and then the primary key ('user.id'
).
Left Join / Right Join Clause
If you would like to perform a "left join" or "right join" instead of an "inner join", use the leftJoin or rightJoin methods. These methods have the same signature as the join method:
Cross Join Clause
You may use the crossJoin method to perform a "cross join". Cross joins generate a cartesian product between the first table and the joined table:
Where Clauses
You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.
For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:
For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Foxdb will assume you would like to use the = operator:
As previously mentioned, you may use any operator that is supported by your database system:
Or Where Clauses
When chaining together calls to the query builder's where method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:
If you need to group an "or" condition within parentheses, you may pass a closure as the first argument to the orWhere method:
The example above will produce the following SQL:
select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
Where Not Clauses
The whereNot
and orWhereNot
methods may be used to negate a given group of query constraints. For example, the following query excludes products that are on clearance or which have a price that is less than ten:
Additional Where Clauses
whereBetween / orWhereBetween
The whereBetween
method verifies that a column's value is between two values:
whereNotBetween / orWhereNotBetween
The whereNotBetween
method verifies that a column's value lies outside of two values:
whereIn / whereNotIn / orWhereIn / orWhereNotIn
The whereIn
method verifies that a given column's value is contained within the given array:
The whereNotIn
method verifies that the given column's value is not contained in the given array:
whereNull / whereNotNull / orWhereNull / orWhereNotNull
The whereNull
method verifies that the value of the given column is NULL:
The whereNotNull
method verifies that the column's value is not NULL:
whereDate / whereMonth / whereDay / whereYear / whereTime
The whereDate
method may be used to compare a column's value against a date:
The whereMonth
method may be used to compare a column's value against a specific month:
The whereDay
method may be used to compare a column's value against a specific day of the month:
The whereYear
method may be used to compare a column's value against a specific year:
The whereTime
method may be used to compare a column's value against a specific time:
whereColumn / orWhereColumn
The whereColumn
method may be used to verify that two columns are equal:
You may also pass a comparison operator to the whereColumn
method:
Ordering, Grouping, Limit & Offset
Ordering
The orderBy Method
The orderBy
method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy
method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:
To sort by multiple columns, you may simply invoke orderBy as many times as necessary:
The latest & oldest Methods
The latest
and oldest
methods allow you to easily order results by date. By default, the result will be ordered by the table's created_at
column. Or, you may pass the column name that you wish to sort by:
Random Ordering
The inRandomOrder method may be used to sort the query results randomly. For example, you may use this method to fetch a random user:
Grouping
The groupBy
& having
Methods
As you might expect, the groupBy
and having
methods may be used to group the query results. The having
method's signature is similar to that of the where method:
You may pass multiple arguments to the groupBy method to group by multiple columns:
To build more advanced having statements, see the havingRaw method.
Limit & Offset
The skip & take Methods
You may use the skip
and take
methods to limit the number of results returned from the query or to skip a given number of results in the query:
Alternatively, you may use the limit
and offset
methods. These methods are functionally equivalent to the take and skip methods, respectively:
Insert Statements
The query builder also provides an insert
method that may be used to insert records into the database table. The insert method accepts an array of column names and values:
Auto-Incrementing IDs
If the table has an auto-incrementing id, use the insertGetId
method to insert a record and then retrieve the ID:
Update Statements
In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column and value pairs indicating the columns to be updated. The update method returns the number of affected rows. You may constrain the update query using where clauses:
Increment & Decrement
The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Both of these methods accept at least one argument: the column to modify. A second argument may be provided to specify the amount by which the column should be incremented or decremented:
Delete Statements
Special features:
You can use the more enjoyable Syntax, which in addition to shortening the code, also helps to make the code more readable
Methods: is / true / false
To create queries based on boolean, you can use true
and false
or is
methods
Methods: and / or / in
You don't need to use the where method for your queries consecutively. You can use the and method or use or instead of orWhere.
Example:
Other methods are also available, such as the following methods:
not(..)
/ orNot(..)
in(..)
/ notIn(..)
/ orIn(..)
/ orNotIn(..)
like(..)
/ orLike(..)
null(..)
/ orNull(..)
/ notNull(..)
/ orNotNull(..)
date(..)
/ orDate(..)
year(..)
/ orYear(..)
month(..)
/ orMonth(..)
day(..)
/ orDay(..)
time(..)
/ orTime(..)