2

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?

1
  • 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 0:26
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.