Filter data from 2 tables and get results from both tables

How can I filter data-set in 2 Tables where second table have more results 1:n.

In first table I can use orWhere and I am getting right data, but my another Table contain multiple results and if I use contain or matching I am getting only data from second Table.

So, I want to filter both tables and get matched data.

Here is my query:

First query to filter first table

$query
      ->where([
        'OR' => [
          'Orders.id' => $freeText,
          'Orders.postal' => $freeText,
          'Orders.address LIKE' => '%' . $freeText . '%',
          'Orders.city LIKE' => '%' . $freeText . '%',
          'Users.first_name' => $freeText,
          'Users.last_name' => $freeText,
          'ProjectAddresses.cost_centre' => $freeText,
          'CONCAT(first_name, last_name) LIKE' => '%' . str_replace(' ', '', $freeText) . '%',
          'CONCAT(first_name, last_name) LIKE' => '%' . $freeText . '%',
          'Users.first_name IN ' => $splittedKeywords,
          'Users.last_name IN ' => $splittedKeywords,
        ]
      ]);

Second query - try to filter data from second Table (but still need matched data from first table)

$query->contain('Items', function ($q) use ($freeText) {
    return $q->where(['vessel_id' => $freeText]);
});

So problem is if I use second query he automatically take only data from second table and my goal is to get all filtered data (from first and second table).

I have 20+ data-sets like:

(int) 0 => [
        'id' => (int) 1,
        'uuid' => '5f34ecda-6bc6-46ed-b5cc-b2227029aed8',
        'user_id' => (int) 319,
        'status' => (int) 30,
        'order_price' => (float) 341.04,
        'address_id' => (int) 379,
        'address' => 'XYZ',
        'building_number' => '171',
        'postal' => '111',
        'city' => 'XYZ',
        'country' => 'AT',
        'project_address' => [
            'id' => (int) 379,
            'type' => 'project',
            'group_id' => (int) 3,
            'default' => false,
            'corresponding_invoice_address' => null,
            'short_name' => 'XYT',
            'comment' => '',
        ],
        'user' => [
            'id' => (int) 319,
            'uuid' => '675216eb-7110-44d2-82a7-f7f020e934a6',
            'title' => 'Herr',
            'first_name' => 'Test',
            'last_name' => 'Test',
        ],
        'item_groups' => [],
        'items' => [
            (int) 0 => [
                'id' => (int) 26,
                'uuid' => 'f4f629be-e25e-4432-8d97-6b2adcee9065',
                'item_group_id' => null,
                'type' => (int) 2,
                'status' => (int) 30,
                'vessel_id' => (int) 40001,
                'features' => [],
            ],
            (int) 1 => [
                'id' => (int) 28,
                'uuid' => 'f4f629be-e25e-4432-8d97-6b2adcee9065',
                'item_group_id' => null,
                'type' => (int) 2,
                'status' => (int) 30,
                'vessel_id' => (int) 40003,
                'features' => [],
            ],
            (int) 1 => [
                'id' => (int) 29,
                'uuid' => 'f4f629be-e25e-4432-8d97-6b2adcee9065',
                'item_group_id' => null,
                'type' => (int) 2,
                'status' => (int) 30,
                'vessel_id' => (int) 40003,
                'features' => [],
            ],
        ]
    ],

SQL

SELECT *
FROM orders Orders
INNER JOIN users Users ON Users.id = (Orders.user_id)
LEFT JOIN addresses ProjectAddresses ON ProjectAddresses.id = (Orders.address_id)
WHERE (Orders.id = :c0
       OR Orders.postal = :c1
       OR Orders.address LIKE :c2
       OR Orders.city LIKE :c3
       OR Users.first_name = :c4
       OR Users.last_name = :c5
       OR ProjectAddresses.cost_centre = :c6
       OR CONCAT(first_name, last_name) LIKE :c7
       OR Users.first_name IN (:c8)
       OR Users.last_name IN (:c9))

Parameter is c1 = 4001 || %40001% || %40001

@ndm The goal is If somebody send in $freeText == 40003 I have to get as result this object where vessel_id = 40003 and thats works, but if somebody send in $freeText == Test then I need again same result because first_name == Test 8see first query) and when in second wuery I am using matching/contain this results are removed because he only "fetch" rows that are matching/contain Items...

Basically I want to check 10+ columns with given $freeText variable and if it is matching I want that results in my data-set (from both tables)

Solution

If I understood you correctly, then you're looking for a left join filter, ie left join Items (additionally to containing it for retrieval), group by Orders primary key (to avoid duplicates), and then simply add the Items.vessel_id condition to the main queries WHERE clause in order to make it a OR condition too.

$query
    ->contain('Items')
    ->leftJoinWith('Items')
    ->where([
        'OR' => [
            'Orders.id' => $freeText,
            // ...
            'Items.vessel_id' => $freeText
        ]
    ])
    ->groupBy('Orders.id');