I believe that almost all the web applications have a function Search, thus you might need to construct a query like:

1
2
3
4
SELECT *
FROM foo INNER JOIN bar ON foo.id = bar.foo_id
WHERE (foo.attr_1 LIKE '%abc%' OR foo.attr_2 LIKE '%abc%')
AND (bar.attr_1 LIKE '%xyz%' OR bar.attr_2 LIKE '%xyz%')

The OR is grouped together with AND, I’ll show how to construct this query in ZF2

In your model table, says ./module/Application/src/Application/Model/FooTable.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?php
namespace Application\Model;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Predicate;

class FooTable
{
    protected $table_gateway;
    protected $sql;

    public function __construct(TableGateway $table_gateway)
    {
        $this->table_gateway = $table_gateway;
        $this->sql = new Sql($this->table_gateway->adapter);
    }
    ...

    public function searchByKeyword($keyword_foo, $keyword_bar)
    {
        $result_set = $this->table_gateway->select(function(Select $select) use ($keyword_foo, $keyword_bar) {
            $select
                ->join(
                    'bar',
                    'foo.id = bar.foo_id',
                    array(
                        'bar_attr' => 'attr',
                        ...
                    )
                )
                ->where(array(
                    new Predicate\PredicateSet(
                        array(
                            new Predicate\Like('foo.attr_1', '%' . $keyword_foo . '%'),
                            new Predicate\Like('foo.attr_2', '%' . $keyword_foo . '%'),
                        ),
                        Predicate\PredicateSet::COMBINED_BY_OR
                    ),
                ));
            ;
            if ($keyword_bar) {
                $select->where(array(
                    new Predicate\PredicateSet(
                        array(
                            new Predicate\Like('bar.attr_1', '%' . $keyword_bar . '%'),
                            new Predicate\Like('bar.attr_2', '%' . $keyword_bar . '%'),
                        ),
                        Predicate\PredicateSet::COMBINED_BY_OR
                    ),
                ));
            }
            // print out the search string for verification
            echo $this->sql->getSqlStringForSqlObject($select);
        });
        return $result_set;
    }
    ...
}

In your controller

1
2
3
4
5
6
7
8
<?php
...
public function searchAction()
{
    $result = $this->getFooTable()->searchByKeyword('abc', 'xyz');
    print_r($result); // verify the results
}
...

References: