PFDB

Colors

:key	id	name
1	1	Red
2	2	Green
3	3	Blue
4	4	Yellow
5	5	Brown

Marks

:key	id	name
1	1	Peugeot
2	2	Citroen
3	3	Nissan

Vehicles

:key	id	color_id	mark_id	price
5	5	1	1	1550
14	14	2	1	1200
22	22	3	1	1400
31	31	1	2	2000
45	45	4	2	1800
52	52	5	2	1400
67	67	5	3	1700
71	71	2	3	1750
89	89	1	3	1500

All vehicles with corresponding names

:key	id	color	mark_id	mark_name	price
5	5	Red	1	Peugeot	1550
14	14	Green	1	Peugeot	1200
22	22	Blue	1	Peugeot	1400
31	31	Red	2	Citroen	2000
45	45	Yellow	2	Citroen	1800
52	52	Brown	2	Citroen	1400
67	67	Brown	3	Nissan	1700
71	71	Green	3	Nissan	1750
89	89	Red	3	Nissan	1500

$vehicles->find()
    ->select(
        'id',
        $vehicles->fields()->parent(
            'color_id',  // field name
            $colors,     // foreign table
            'name'       // foreign name
        ),
        $vehicles->fields()->parent(
            'mark_id',
            $marks
        ),
        'price'
    )

Filter on color name without displaying it

:key	id	color_id	mark_id	price
5	5	1	1	1550
31	31	1	2	2000
89	89	1	3	1500

$vehicles->find(
    $vehicles->expr()->eq(
        $vehicles->fields()->parent(
            'color_id',  // field name
            $colors,     // foreign table
            'name'       // foreign name
        ),
        'Red'
    )
)

Multiple filters (color + mark)

:key	id	color_id	mark_id	price
5	5	1	1	1550
31	31	1	2	2000

$vehicles->find(
    $vehicles->expr()->eq(
        $vehicles->fields()->parent(
            'color_id',  // field name
            $colors,     // foreign table
            'name'       // foreign name
        ),
        'Red'
    ),
    $vehicles->expr()->contains(
        $vehicles->fields()->parent(
            'mark_id',   // field name
            $marks,      // foreign table
            'name'       // foreign name
        ),
        'o'
    )
)