Retrieving Data

Using the query builder

DatabaseFactory provides a simple, robust and intuitive Query Builder API that grants us the ability to easily work with our database tables. In order to utilize it, we need to call the table() method or use the db_factory() helper function, and pass through the database table we want to work with. To see how to customize your queries, go here.

# initiate the query builder using the facade system
$users = DatabaseFactory\Facades\DB::table('users');

# OR

# initiate the query builder using the helper function
$users = db_factory('users');

# generate the query
$users->select('name, email')->get();

# the SQL to be executed
SELECT name, email FROM users

Let's try a different, more robust query with a WHERE, LIMIT and ORDER BY

# generate the query
$users->select('name, email')
      ->where('name', '=', 'Mark')
      ->orderBy('name', 'DESC')
      ->limit(10))
      ->get();

# the SQL to be executed
SELECT name, email FROM users WHERE name = 'Mark' ORDER BY name DESC LIMIT 10

Using the ORM

DatabaseFactory also provides an intuitive ORM (Object Relational Mapper) to work with data which uses an entity class to model that data (see here). The ORM has many shortcut methods which give us the ability to generate our queries with minimal code.

Selecting all columns [default behavior]

# generate the query
User::where('name', '<>', '');

# the SQL to be executed
SELECT * FROM users WHERE name <> ''

Selecting other columns by passing them as a string via the final argument (optional)

# generate the query
User::where('name', '<>', '', 'email, name');

# the SQL to be executed
SELECT email, name FROM users WHERE users.name <> ''

We can also write the above queries in a shorthand version, like so:

# generate the query
User::whereNot('name', '');

# the SQL to be executed
SELECT * FROM users WHERE name <> ''

Selecting other columns by passing them as a string via the final argument (optional)

# generate the query
User::whereNot('name', '', 'email, name');

# the SQL to be executed
SELECT email, name FROM users WHERE name <> ''

Using them together

Naturally, you can use the ORM and Query Builder in conjunction with one another to build complex queries, by calling the query() method.

# generate the query
User::query()->whereNot('name', '', 'email, name')->limit(10)->get();

# the SQL to be executed
SELECT email, name FROM users WHERE name <> '' LIMIT 10

Using entities

Once we've built a collection of data, we can use the entity system to access that data. This is done by accessing the properties of the entity object. For more information on how DatabaseFactory utilizes the entity approach to modeling data, go here.

Collecting all records within a table

# generate a collection of all records
$users = User::all();

# the SQL to be executed
SELECT * FROM users

Collecting one record based on its ID

# generate a collection of one record based 
# on its ID
$user = User::find(15);

# the SQL to be executed
SELECT * FROM users WHERE id = '15'

Advanced Usage

DatabaseFactory is meant to be extremely robust. Using DatabaseFactory, we can build some rather complex queries. For example, here is a more comprehensive query that can be generated using several subcomponents together..

# generate the query
$users->join('users_roles', ['users_roles.user_id', 'users.id'])
      ->and('users.email', '<>', '')
      ->and('users.name', '<>', '')
      ->andLike('users.email', 'proton')
      ->notLike('users.email', 'gmail')
      ->groupBy('users.id')
      ->orderBy('users.id', 'DESC')
      ->limit(10)
      ->offset(5)
      ->get();

# the SQL to be executed
SELECT * FROM users 
JOIN users_roles ON users_roles.user_id = users.id
  AND users.email <> ''
  AND users.name <> ''
  AND users.email LIKE '%proton%'
  WHERE users.email NOT LIKE '%gmail%'
GROUP BY users.id
ORDER BY users.id DESC
LIMIT 10 OFFSET 5

Selecting other columns by passing them as a string via the final argument (optional)

# generate the query
$users->join('users_roles', ['users_roles.user_id', 'users.id'], 'users.name, users.email')
      ->and('users.email', '<>', '')
      ->and('users.name', '<>', '')
      ->andLike('users.email', 'proton')
      ->notLike('users.email', 'gmail')
      ->groupBy('users.id')
      ->orderBy('users.id', 'DESC')
      ->limit(10)
      ->offset(5)
      ->get();

# the SQL to be executed
SELECT users.name, users.email FROM users 
JOIN users_roles ON users_roles.user_id = users.id
  AND users.email <> ''
  AND users.name <> ''
  AND users.email LIKE '%proton%'
  WHERE users.email NOT LIKE '%gmail%'
GROUP BY users.id
ORDER BY users.id DESC
LIMIT 10 OFFSET 5

Raw SQL

If you want to execute raw SQL using DatabaseFactory, you can easily call the query() method on the DB facade. It is important to note that the query() method does not use PDO::prepare() to execute a parameterized statement. Instead, it acts as a wrapper for PDO::query().

$users = DatabaseFactory\Facades\DB::query('SELECT * FROM USERS');