relatedTo - return only entries that have a related entry where a field in the related entry has a value

Question

Trying to figure out if this is possible in 1 step. I want to return locations that have a related entry in the relatedMerchant field, but I'd also want to make sure that a field in the relatedMerchant entry has a value...

$locationsRelatedToCategories = Entry::find()
   ->section('locations')
   ->limit(100000)
   ->relatedTo([
     // somewhat unrelated, but this entry should be related to a specific category
     'targetElement' => $categoryElement, 'field' => 'locationOffers.category'
   ])
   ->relatedMerchant(':notempty:') // has to have a related merchant
   // somewhere here, how can I return only relatedMerchant entries where relatedMerchant.someField :notempty:
   ->all();

Answer 1

If your craft_searchindex table (assume your table prefix is "craft_") isn't too big (I think 100k rows is big), and your field in "relatedMerchant" is the 'title' field.

You can do it in one step.

$locationsRelatedToCategories = Entry::find()
   ->section('locations')
   ->limit(100000)
   ->relatedTo([
     'targetElement' => $categoryElement, 'field' => 'locationOffers.category'
   ])
   ->search('-relatedMerchant:*') 
   ->all();

Otherwise, I don't think its possible. You will need to query the relatedMerchant entry to get ids(). And relate the id array to locationsRelatedToCategories.

$relatedMerchantQuery = Entry::find()->section('relatedMerchangt');
$relatedMerchantQuery->field = ':notempty:';
$relatedMerchantIds = $relatedMerchantQuery->ids();

$locationsRelatedToCategories = Entry::find()
   ->section('locations')
   ->limit(100000)
   ->relatedTo($relatedMerchantIds)
   ->all();

Craft only store element relations via craft_relations table. The elementQuery->relatedTo() method is the only way to access it, without creating custom query or tables.