A scenario here:

A Q & A website that allow user to ask question and put several tags to that question.

Database tables:

tag 

1
2
3
4
5
6
7
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| title        | varchar(30) | NO   |     | NULL    |                |
| description  | varchar(100)| NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

tag_rel 

1
2
3
4
5
6
7
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| tag_id       | varchar(30) | NO   |     | NULL    |                |
| question_id  | varchar(100)| NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

question 

1
2
3
4
5
6
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| description  | varchar(100)| NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Now want to get those tags related to a tag with ID 100, the query could be

1
2
3
4
5
6
7
8
9
10
11
SELECT tag.*
FROM tag
INNER JOIN tag_rel ON tag.id = tag_rel.tag_id
WHERE tag.id <> 100
AND tag_rel.question_id IN
(
    SELECT tag_rel.question_id
    FROM tag
    INNER JOIN tag_rel ON tag.id = tag_rel.tag_id
    WHERE tag.id = 100
)

Now want to construct this query in Zend Frameword 2

In your model table, says ./module/Application/src/Application/Model/TagTable.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
61
<?php
namespace Application\Model;

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

class TagTable
{
    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 getRelatedTagsByTag($id)
    {
        $result_set = $this->table_gateway->select(function(Select $select) use ($id) {
            $subselect = $this->sql->select();
            $subselect
                ->columns(array())
                ->from('tag')
                ->join(
                    'tag_rel',
                    'tag.id = tag_rel.tag_id',
                    array(
                        'question_id',
                    )
                )
                ->where(array('tag_rel.tag_id = ?' => $id,))
            ;

            $select
                ->join(
                    'tag_rel',
                    'tag.id = tag_rel.tag_id',
                    array(
                    )
                )
                ->where(array('tag.id <> ?' => $id))
                ->where->in('tag_rel.question_id', $subselect)
            ;
            // uncomment the next line to see your query
            // echo $select->getSqlString();
        });

        // don't use DISTINCT in sql due to performance issue
        // Get unique records
        $distincted_result = array();
        foreach ($result_set as $rowset) {
            if (!in_array($rowset, $distincted_result)) {
                $distincted_result[] = $rowset;
            }
        }
        return $distincted_result;
    }
    ...
}

The performance may be sux, in order to solve this, just index tag_rel.tag_id and tag_rel.question_id.

1
2
mysql > ALTER TABLE `tag_rel` ADD INDEX (`tag_id`);
mysql > ALTER TABLE `tag_rel` ADD INDEX (`question_id`);

References: