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: