/**
* Possible Joins to make depending on specifications passed.
* @return Array of Illuminate\Database\Query\JoinClause
*/
protected function getAvailableJoinClauses()
{
//join with the test_users table
$user_join = new JoinClause('inner', 'test_users');
$user_join->on('test_users.id', '=', 'test_posts.user_id');
return [$user_join];
}
/**
* Add a join clause to the query.
*
* @param string $table
* @param string $one
* @param string $operator
* @param string $two
* @param string $type
* @param bool $where
* @return $this
*/
public function join($table, $one, $operator = null, $two = null, $type = 'inner', $where = false)
{
// If the first "column" of the join is really a Closure instance the developer
// is trying to build a join with a complex "on" clause containing more than
// one condition, so we'll add the join and call a Closure with the query.
if ($one instanceof Closure) {
$join = new JoinClause($type, $table);
call_user_func($one, $join);
$this->joins[] = $join;
$this->addBinding($join->bindings, 'join');
} else {
$join = new JoinClause($type, $table);
$this->joins[] = $join->on($one, $operator, $two, 'and', $where);
$this->addBinding($join->bindings, 'join');
}
return $this;
}
/**
* @param JoinClause $query
* @param string $column
* @param mixed $values
* @param string $operator
*
* @throws \InvalidArgumentException
*/
private function transformCustomConstraint($query, $column, $values, $operator)
{
if (is_string($column) && !Str::contains($column, '.')) {
$column = $this->related->getTable() . '.' . $column;
}
$values = $this->transformConstraintObject($values);
if (is_array($values)) {
if (count($values) > 0) {
switch ($operator) {
case '=':
$query->whereIn($column, $values);
break;
case '!=':
$query->whereNotIn($column, $values);
break;
case 'b':
$query->where($column, '>=', $this->transformConstraintObject($values[0]))->where($column, '<=', $this->transformConstraintObject($values[1]));
break;
case '!b':
$query->where($column, '<=', $this->transformConstraintObject($values[0]))->orWhere($column, '>=', $this->transformConstraintObject($values[1]));
break;
default:
throw new \InvalidArgumentException('Join constraint has an invalid operator defined.');
break;
}
}
} else {
$query->where($column, $operator, $values);
}
}
/**
* Add a join clause to the query.
*
* @param string $table
* @param string $first
* @param string $operator
* @param string $second
* @param string $type
* @return \Illuminate\Database\Query\Builder|static
*/
public function join($table, $first, $operator = null, $second = null, $type = 'inner')
{
// If the first "column" of the join is really a Closure instance the developer
// is trying to build a join with a complex "on" clause containing more than
// one condition, so we'll add the join and call a Closure with the query.
if ($first instanceof Closure) {
$this->joins[] = new JoinClause($type, $table);
call_user_func($first, end($this->joins));
} else {
$join = new JoinClause($type, $table);
$join->on($first, $operator, $second);
$this->joins[] = $join;
}
return $this;
}
public function testGetBuilder()
{
$post = new Post();
$builder = $this->apiHandler->parseMultiple($post, ['title', 'description'], $this->params)->getBuilder();
$queryBuilder = $builder->getQuery();
//
// Fields
//
$columns = $queryBuilder->columns;
$this->assertContains('description', $columns);
$this->assertContains('title', $columns);
//
// Filters
//
$wheres = $queryBuilder->wheres;
//Test the nested filters
foreach ($wheres as $where) {
if ($where['type'] == 'Nested') {
$query = $where['query'];
$subWheres = $query->wheres;
if ($subWheres[0]['boolean'] == 'and') {
//assert for title-not
$this->assertEquals(['type' => 'Basic', 'column' => 'posts.title', 'operator' => '!=', 'value' => 'Example Title', 'boolean' => 'and'], $subWheres[0]);
$this->assertEquals(['type' => 'Basic', 'column' => 'posts.title', 'operator' => '!=', 'value' => 'Another Title', 'boolean' => 'and'], $subWheres[1]);
} else {
//assert for title-lk
$this->assertEquals(['type' => 'Basic', 'column' => 'posts.title', 'operator' => 'LIKE', 'value' => 'Example Title', 'boolean' => 'or'], $subWheres[0]);
$this->assertEquals(['type' => 'Basic', 'column' => 'posts.title', 'operator' => 'LIKE', 'value' => 'Another Title', 'boolean' => 'or'], $subWheres[1]);
}
}
}
//assert for title
$this->assertContains(['type' => 'Basic', 'column' => 'posts.title', 'operator' => '=', 'value' => 'Example Title', 'boolean' => 'and'], $wheres);
//assert for title-not-lk
$this->assertContains(['type' => 'Basic', 'column' => 'posts.title', 'operator' => 'NOT LIKE', 'value' => 'Example Title', 'boolean' => 'and'], $wheres);
//assert for id-min
$this->assertContains(['type' => 'Basic', 'column' => 'posts.id', 'operator' => '>=', 'value' => 5, 'boolean' => 'and'], $wheres);
//assert for id-max
$this->assertContains(['type' => 'Basic', 'column' => 'posts.id', 'operator' => '<=', 'value' => 6, 'boolean' => 'and'], $wheres);
//assert for id-gt
$this->assertContains(['type' => 'Basic', 'column' => 'posts.id', 'operator' => '>', 'value' => 7, 'boolean' => 'and'], $wheres);
//assert for id-st
$this->assertContains(['type' => 'Basic', 'column' => 'posts.id', 'operator' => '<', 'value' => 8, 'boolean' => 'and'], $wheres);
//assert for id-in
$this->assertContains(['type' => 'In', 'column' => 'posts.id', 'values' => ['1', '2'], 'boolean' => 'and'], $wheres);
//assert for id-not-in
$this->assertContains(['type' => 'NotIn', 'column' => 'posts.id', 'values' => ['3', '4'], 'boolean' => 'and'], $wheres);
//assert for relation (user->first_name like)
$this->assertContains(['type' => 'Basic', 'column' => 'comments.title', 'operator' => 'LIKE', 'value' => 'This comment', 'boolean' => 'and'], $wheres);
$join = new JoinClause('inner', 'comments');
$join->on('posts.id', '=', 'comments.customfk_post_id');
$this->assertEquals($join, $queryBuilder->joins[0]);
//
// Limit
//
$limit = $queryBuilder->limit;
$this->assertEquals($this->params['_limit'], $limit);
//
// Offset
//
$offset = $queryBuilder->offset;
$this->assertEquals($this->params['_offset'], $offset);
//
// Sort
//
$orders = $queryBuilder->orders;
$this->assertContains(['column' => 'title', 'direction' => 'desc'], $orders);
$this->assertContains(['column' => 'first_name', 'direction' => 'asc'], $orders);
//
//With
//
$eagerLoads = $builder->getEagerLoads();
$this->assertArrayHasKey('comments', $eagerLoads);
$this->assertArrayHasKey('comments.user', $eagerLoads);
//Check if auto fields are set on the base query
$this->assertContains('posts.id', $columns);
//Check if fields are set on the "comments" relation query
$query = $post->newQuery();
call_user_func($eagerLoads['comments'], $query);
$columns = $query->getQuery()->columns;
$this->assertContains('title', $columns);
$this->assertContains('customfk_post_id', $columns);
$this->assertContains('user_id', $columns);
//Check if fields are set on the "comments.user" relation query
$query = $post->newQuery();
call_user_func($eagerLoads['comments.user'], $query);
$columns = $query->getQuery()->columns;
$this->assertContains('id', $columns);
$this->assertContains('first_name', $columns);
//Check if sorts are set on the "comments" relation query
$query = $post->newQuery();
call_user_func($eagerLoads['comments'], $query);
$orders = $query->getQuery()->orders;
$this->assertContains(['column' => 'created_at', 'direction' => 'asc'], $orders);
//
// Fulltext search
//
$builder = $this->apiHandler->parseMultiple($post, ['title', 'description'], ['_q' => 'Something to search'])->getBuilder();
$queryBuilder = $builder->getQuery();
$wheres = $queryBuilder->wheres;
//Test the nested filters
foreach ($wheres as $where) {
if ($where['type'] == 'Nested') {
$query = $where['query'];
$subWheres = $query->wheres;
$this->assertEquals(['type' => 'Basic', 'column' => 'title', 'operator' => 'LIKE', 'value' => '%Something%', 'boolean' => 'or'], $subWheres[0]);
$this->assertEquals(['type' => 'Basic', 'column' => 'title', 'operator' => 'LIKE', 'value' => '%to%', 'boolean' => 'or'], $subWheres[1]);
$this->assertEquals(['type' => 'Basic', 'column' => 'title', 'operator' => 'LIKE', 'value' => '%search%', 'boolean' => 'or'], $subWheres[2]);
$this->assertEquals(['type' => 'Basic', 'column' => 'description', 'operator' => 'LIKE', 'value' => '%Something%', 'boolean' => 'or'], $subWheres[3]);
$this->assertEquals(['type' => 'Basic', 'column' => 'description', 'operator' => 'LIKE', 'value' => '%to%', 'boolean' => 'or'], $subWheres[4]);
$this->assertEquals(['type' => 'Basic', 'column' => 'description', 'operator' => 'LIKE', 'value' => '%search%', 'boolean' => 'or'], $subWheres[5]);
}
}
$builder = $this->apiHandler->parseMultiple($post, ['title', 'description'], ['_q' => 'Something to search'])->getBuilder();
$queryBuilder = $builder->getQuery();
//Test the where
$wheres = $queryBuilder->wheres;
$this->assertEquals(['type' => 'raw', 'sql' => 'MATCH(title,description) AGAINST("Something to search" IN BOOLEAN MODE)', 'boolean' => 'and'], $wheres[0]);
//Test the select
$columns = $queryBuilder->columns;
$this->assertContains($this->fulltextSelectExpression, $columns);
$this->assertContains('*', $columns);
}