Querying relational data in plugin

Question

I'm creating a plugin and I need help to query relational data.

Here's the table schema I have:

collection
+----+-------------+---------------------+---------------------+-----+
| id | label       | dateCreated         | dateUpdated         | uid |
+----+-------------+---------------------+---------------------+-----+
|  1 | Static page | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
+----+-------------+---------------------+---------------------+-----+

collection_section
+----+---------------+-----------+---------------------+---------------------+-----+
| id | collectionId  | sectionId | dateCreated         | dateUpdated         | uid |
+----+---------------+-----------+---------------------+---------------------+-----+
|  1 |             1 |        16 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
|  2 |             1 |         9 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
|  3 |             1 |        14 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
|  4 |             1 |        10 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
+----+---------------+-----------+---------------------+---------------------+-----+

craft_content
+------------+
| title      |
+------------+
| mytitle    |
+------------+

I've created 2 records:

// collectionRecord
public function defineRelations()
{
    return array(
        'collectionSection' => array(
            static::HAS_MANY,
            'collectionSectionRecord',
            'sectionId'
        )
    );
}

// collectionSectionRecord
public function defineRelations()
{
    return array(
        'collection' => array(
            static::BELONGS_TO,
            'collectionRecord',
            'required' => true,
            'onDelete' => static::CASCADE
        ),
        'section'  => array(
            static::BELONGS_TO,
            'SectionRecord',
            'required' => true,
            'onDelete' => static::CASCADE
        ),
    );
}

The section ids are only singles and I need to get a listing of all the sections with their content related to the collectionId.

I tried this:

$query = craft()->db->createCommand()
    ->select('s.*')
    ->from('sections s')
    ->join('collection_section cs', 'cs.sectionId = s.id')
    ->where(array('cs.collectionId' => 1))
    ->queryAll();
return SectionModel::populateModels($query);

I actually get the sections from the craft_sections table but I need the related content (title row) "mytitle" in the craft_content table. Is there an easy way to do that in Craft?

So, do you want this done through ActiveRecord or through querybuilder? To do the latter the relationships for AR don't matter - you're building the SQL query manually through querybuilder. — Brad Bell, Apr 26 '16 at 00:26

score 3 · Answer 1 · answered Apr 27 '16 at 17:22

I ended up doing it this way:

$records = Plugin_CollectionSectionRecord::model()
        ->findAllByAttributes(['collectionId' => $collectionId]);
}

$ids = [];
foreach($records as $value) {
    $ids[] = $value->sectionId;
}

$criteria = craft()->elements->getCriteria(ElementType::Entry);
$criteria->elementType = 'Entry';
$criteria->sectionId = $ids;
$result = $criteria->all();
return $result;

Querying relational data in plugin

1 Answers1