Selects

Create a select query object with the connection object.

string dsn = "Database=test;Data Source=localhost;User Id=root;Password=;SslMode=none"
MySqlConnection connection = new MySqlConnection(dsn);
connection.Open();

Query query = new Query(connection);

Inspecting The Query

Getting the generated SQL string:

query.From("users AS u");
query.Select("*");

string sql = query.GetSql();
Console.WriteLine(sql);

Output:

SELECT * FROM `users` as `u`;

Retrieving Results

Retrieving All Rows From A Table

You may use the select() method of the Connection object 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 get the results using the get method:

Query query = this.NewQuery();

query.From("users");
query.Select(new List<object>
{
    "id",
    "username",
    "email"
});

var rows = query.Execute().FetchAll();

foreach (var row in rows)
{
    var id = row.Get<int>("id");
    var username  = row.Get<string>("username");
    var email = row.Get<string>("email");
}

Retrieving All Rows From A Table as POCO

Example POCO class:

public class User
{
    public long Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
}

Now query the list of User POCO’s.

Query query = this.NewQuery();

query.From("users");
query.Select(new List<object>
{
    "id",
    "username",
    "email"
});

var users = query.Execute().FetchAll<User>();
    
foreach (var user in users)
{
    var id = user.Id;
    var username = user.Username;
    var email = user.Email;    
}

Retrieving A Single Row From A Table

The Fetch() method returns an row containing the results where each result is an instance of the Array or PHP StdClass object. You may access each column’s value by accessing the column as a property of the object:

var row = query.Execute().Fetch();

Retrieving A Single Column From A Table as POCO

User user = query.Execute().Fetch<User>();

Distinct

The distinct method allows you to force the query to return distinct results:

Columns

Select columns by name:



SELECT `id`,`username`,`first_name` AS `firstName` FROM `users`;

Select columns by array:

Select columns with alias:

Add fields one after another:



SELECT `first_name`,`last_name`,`email` FROM `users`;

Raw Expressions

Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points!

To create a raw expression, you may use the RawExp value object:



SELECT count(*) AS user_count, `status` FROM `payments` WHERE `status` <> 1 GROUP BY `status`;

Creating raw expressions with the function builder:



SELECT count(*) AS user_count,`status` FROM `payments`;

Aggregates

The query builder also provides a RawExp for aggregate methods such as count, max, min, avg, and sum.

You may call any of these methods after constructing your query:



SELECT MAX(amount), MIN(amount) FROM `payments`;

Sub Selects

If you want to SELECT FROM a subselect, do so by passing a callback function and define an alias for the subselect:

SELECT `id`,(SELECT MAX(payments.amount) FROM `payments`) AS `max_amount` FROM `test`;

Joins

Inner Join Clause

The query builder may also be used to write join statements. 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. Of course, as you can see, you can join to multiple tables in a single query:



SELECT `users`.*, `contacts`.`phone`, `orders`.`price` 
FROM `users`
INNER JOIN `contacts` ON `users`.`id` = `contacts`.`user_id`
INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`;

Left Join Clause

If you would like to perform a “left join” instead of an “inner join”, use the leftJoin method. The leftJoin method has the same signature as the join method:



SELECT *
FROM `users`
LEFT JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

Cross Join Clause

From the MySQL JOIN docs:

In MySQL, CROSS JOIN is syntactically equivalent to INNER JOIN; they can replace each other. In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause; CROSS JOIN is used otherwise.

In MySQL Inner Join and Cross Join yielding the same result.

Please use the join method.

Advanced Join Clauses

You may also specify more advanced join clauses. To get started, pass a (raw) string as the second argument into the joinRaw and leftJoinRaw method.



SELECT `id` FROM `users` AS `u` 
INNER JOIN `posts` AS `p` ON (p.user_id=u.id AND u.enabled=1 OR p.published IS NULL);

Unions

The query builder also provides a quick way to “union” two queries together. For example, you may create an initial query and use the union(), unionAll() and unionDistinct() method to union it with a second query:



SELECT `id` FROM `table1` UNION SELECT `id` FROM `table2`;

Where Clauses

Simple Where Clauses

You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where 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.

Finally, the third argument is the value to evaluate against the column.

For example, here is a query that verifies the value of the “votes” column is equal to 100:



SELECT * FROM `users` WHERE `votes` = 100;

Of course, you may use a variety of other operators when writing a where clause:

You may also pass multiple AND conditions:



SELECT * FROM `users` WHERE `status` = '1' AND `subscribed` <> '1';

Or Statements

ou may chain where constraints together as well as add OR clauses to the query. The orWhere method accepts the same arguments as the where method:



SELECT * FROM `users` WHERE `votes` > '100' OR `name` = 'John';

Additional Where Clauses

Between and not between


SELECT * FROM `users` WHERE `votes` BETWEEN '1' AND '100';



SELECT * FROM `users` WHERE `votes` NOT BETWEEN '1' AND '100';
In and not in


SELECT * FROM `users` WHERE `id` IN ('1', '2', '3');



SELECT * FROM `users` WHERE `id` NOT IN ('1', '2', '3');
Is null and is not null


SELECT * FROM `users` WHERE `updated_at` IS NULL;



SELECT * FROM `users` WHERE `updated_at` IS NOT NULL;

If you use the ‘=’ or ‘<>’ for comparison and pass a null value you get the same result.



SELECT * FROM `users` WHERE `updated_at` IS NULL;

Where Column

The whereColumn method may be used to verify that two columns are equal:



SELECT * FROM `users` WHERE `users`.`id` = `posts`.`user_id`;

The whereColumn method can also be called multiple times to add multiple conditions. These conditions will be joined using the and operator:



SELECT * 
FROM `users` 
WHERE `first_name` = `last_name`
AND `updated_at` = `created_at`;

Complex Where Conditions

Where Raw

Using whereRaw:



SELECT `id`, `username` FROM `users` WHERE status <> 1;

Using RawExp:



SELECT * FROM `users` WHERE users.id = posts.user_id;

Order By



SELECT * FROM `users` ORDER BY `updated_at` ASC;

Group By



SELECT * FROM `users` GROUP BY `role`;

Limit and Offset



SELECT * FROM `users` LIMIT 10;



SELECT * FROM `users` LIMIT 25, 10;

Having



SELECT * 
FROM `users` 
GROUP BY `id`, `username` ASC
HAVING `username` = 'admin';

Complex having conditions:

Using SQL Functions

A number of commonly used functions can be created with the Func() method:

Example:



SELECT SUM(`amount`) AS `sum_amount` FROM `payments`;

Next page: Inserts