Retrieving Data

Developer's Note:

The DatabaseFactory API provides a couple methods for returning data. This includes returning JSON and an array. These methods are currently toArray() and toJSON().

Another feature to take note of is the ability to return a string representation of the query that is being executed. This can be done by calling the toSQL() method. Calling this method, will return the value of DatabaseFactory\Builder::$query.

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.

# generate the query
$users = DatabaseFactory\Facades\DB::table('users');
$users->select('name, email')->where('name', '=', 'Mark');

# the SQL to be executed
SELECT name, email FROM users WHERE name = 'Mark'

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

# generate the query
$users = DatabaseFactory\Facades\DB::table('users');
$users->select('name, email')->where('name', '=', 'Mark')->orderBy('name', 'DESC')->limit(10);

# 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. See the advanced usage section, for more concrete examples.

Selecting all columns [default behavior]

# generate the query
User::whereNot('name', '')->limit(5)->offset(5);

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

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

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

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

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.

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

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 = DatabaseFactory\Facades\DB::table('users);
$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 = DatabaseFactory\Facades\DB::table('users);
$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 ...