Retrieving Data

Using the query builder

DatabaseFactory provides an amazingly 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 and pass through the database table we want to work with. To see how to customize how your query builder instance executes queries, go here.

# generate the query
$users = DatabaseFactory\Facades\DB::table('users');

# Or, we can use the helper function
$users = db_factory('users');

$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 even the most robust 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.

Selecting all columns [default behavior]

# generate the query
User::query()->select('name')->get()

# the SQL to be executed
SELECT name FROM users

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

# loop through each entity object
foreach($users as $user) {
  echo $user->name;
}

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'

# print out the property of the entity object
echo $user->name

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);

# 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);

# 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

TO BE CONTINUED ...