/**
  * 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];
 }
示例#2
0
文件: Builder.php 项目: denise92/cms
 /**
  * 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);
 }