Where are Matrix Entries Fields Relationships in the Database?

Question

I can see the simple matrix field data in the craft_matrixcontent_MYMATRIXtable.

craft_matrixcontent_MYMATRIX
---------------------------
id | elementId | locale | field_BLOCKTYPE_FIELDHANDLE | field_BLOCKTYPE_FIELDHANDLE | dateCreated | dateUpdated | uid

If I need to query upon those fields, I can thanks to some of the great posts to this site. But those fields are simple text fields.

I have an Entries field in my matrix. Where can I find this relationship in the database? It doesn't exist in the craft_matrixcontent_MYMATRIX table.

Answer 1

I think I've figured it out. The sourceId of the craft_relations table is the elementId of the custom matrix table. And the targetId of the craft_content table is the elementId of the `craft_content table.

Code example

Here's what I was trying to do. I needed to filter entries based on a field value in an Entries matrix field on another entry. Whew.

$criteria = craft()->elements->getCriteria(ElementType::Entry);
$criteria->section = 'FIRSTCOOLTHING';

$query = craft()->elements->buildElementsQuery($criteria);

$query->join('matrixblocks matrixblocks', 'matrixblocks.ownerId = entries.id');
$query->join('matrixcontent_MYMATRIX matrixcontent_MYMATRIX', 'matrixcontent_MYMATRIX.elementId = matrixblocks.id');
$query->join('relations relations', 'relations.sourceId = matrixcontent_MYMATRIX.elementId');
$query->join('content SECONDCOOLTHING', 'SECONDCOOLTHING.elementId = relations.targetId');

$query->andWhere('SECONDCOOLTHING.field_FIELDHANDLE = '.$THINGTOSEARCHFOR);

$query->order('title asc');

$queryResults = $query->queryAll();
return EntryModel::populateModels($queryResults);

Answer 2

All relational fields (Entries, Assets, Categories, Tags, etc.) store their relationship information in the boringly named craft_relationships table.

There you can see the field ID, source and target ID as well as the source locale and sort order.