How to build complex queries using like methods?

Questions Components & MVC
1

Hi all,
how can i create an sql output like below using Laminas sql abstraction ?

SELECT * FROM users u 
WHERE u.account_type_id = 'dr' AND 
( 
	( u.firstname LIKE '%john%' OR u.firstname LIKE '%james%' OR u.firstname LIKE '%brown%' ) OR
	( u.lastname LIKE '%john%' OR u.lastname LIKE %'james%' OR u.lastname LIKE '%brown%' )
) ORDER BY u.firstname ASC

I really appreciate any help you can.
Thanks.

$sql    = new Sql($this->adapter);
$select = $sql->select();
$select->columns([]);
$select->from(['u' => 'users']);
$select->where(?);
2

Please have a look at the documentation there you will find an example with nested conditions.

3

Thanks i build it like this but what about the foreach loop ?

        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'user_id',
        ]);
        $nest = $select->where('account_type_id', 'dr')
        	->where->nest()
        	->nest();
         $nest
    		->like('firstname', '%john%')
    		->or->like('firstname', '%james%')
    		->or->like('firstname', '%brown%')
    		->unnest()  // bracket closed
    		->or
    		->nest()
    		->like('lastname', '%john%')
    		->or->like('lastname', '%james%')
    		->or->like('lastname', '%brown%')
    		->unnest()
    		->unnest();

foreach version seems not normal.

        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'user_id',
        ]);
        $nest = $select->where('account_type_id', 'dr')
        	->where->nest()
        	->nest();
    	$names = ['%john%','%james%','%brown%'];
		foreach ($names as $val) {
			$nest = $nest
				->like('firstname', $val)
				->unnest()
				->or
				->nest()
				->like('lastname', $val);
	    }
	    $nest = $nest->unnest();

Actual output:

SELECT `users`.`user_id` AS `user_id` FROM `users` WHERE account_type_id AND (
(`firstname` LIKE '%john%') OR 
(`lastname` LIKE '%john%' AND `firstname` LIKE '%james%') OR (`lastname` LIKE '%james%' AND `firstname` LIKE '%brown%') 
OR (`lastname` LIKE '%brown%')
)
1 Like
4

After thinking for a while i found the solution.

        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'user_id',
        ]);
        $nest = $select->where('account_type_id', 'dr')
        	->where->nest()
        	->nest();

		$name = explode(' ', 'james john brown');
		foreach ($name as $val) {
			$nest = $nest->or->like('firstname', '%'.$val.'%');
		}
		$nest = $nest->unnest();
    	$nest = $nest->or->nest();
    	foreach ($name as $val) {
    		$nest = $nest->or->like('lastname', '%'.$val.'%');
    	}
    	$nest = $nest->unnest();
    	$nest = $nest->unnest();

I think this example should be added to the documentation. :innocent:

1 Like
5 
final public function searchLike(array $columns, $keyword, Select $select)
{

    if (!trim($keyword)) {
        return false;
    }

    $p = [];
    foreach ($columns as $c) {
        $p[] = new Predicate\Like($c, '%' . $keyword . '%');
    }

    if ($p) {
        $select->where([
            new Predicate\PredicateSet(
                $p,
                Predicate\PredicateSet::COMBINED_BY_OR
            ),
        ]);

        return true;
    }

    return false;
}
1 Like
6

Thanks for advanced one,
An another way:

        $columns = [
            'firstname' => array('john', 'james', 'brown'),
            'lastname' => array('john', 'james', 'brown'),
        ];
        $nest = $select->where->nest();
        foreach ($columns as $col => $words) {
            $nest = $nest->or->nest();
            foreach ($words as $str) {
                $nest->or->like(new Expression($col), '%'.$str.'%');
            }
            $nest = $nest->unnest();
        }
        $nest->unnest();