Colors

:keyidname
11Red
22Green
33Blue
44Yellow
55Brown

Marks

:keyidname
11Peugeot
22Citroen
33Nissan

Vehicles

:keyidcolor_idmark_idprice
55111550
1414211200
2222311400
3131122000
4545421800
5252521400
6767531700
7171231750
8989131500

All vehicles with corresponding names

:keyidcolormark_idmark_nameprice
55Red1Peugeot1550
1414Green1Peugeot1200
2222Blue1Peugeot1400
3131Red2Citroen2000
4545Yellow2Citroen1800
5252Brown2Citroen1400
6767Brown3Nissan1700
7171Green3Nissan1750
8989Red3Nissan1500
$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

:keyidcolor_idmark_idprice
55111550
3131122000
8989131500
$vehicles->find(
    $vehicles->expr()->eq(
        $vehicles->fields()->parent(
            'color_id',  // field name
            $colors,     // foreign table
            'name'       // foreign name
        ),
        'Red'
    )
);

Multiple filters (color + mark)

:keyidcolor_idmark_idprice
55111550
3131122000
$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'
    )
);