Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Executing query, returning model instances for every result

I've been using Pagekit before this, which is kinda based on Symfony. However, now I've transitioned to PhalconPHP to get rid of the whole CMS part, and just have a clean framework to build upon.

It feels similar, but obviously there are quite a few differences between them. One of which I haven't yet been able to figure out.

In pagekit, I would be able to use an ORM Query like:

    $topTracks = Media::query()
      ->select('DISTINCT m.*, COUNT(h.id) as popularity, h.played_at as played_at')
      ->from('@shoutzor_media m')
      ->leftJoin('@shoutzor_media_artist ma', 'ma.artist_id = '.$this->id)
      ->leftJoin('@shoutzor_history h', 'h.media_id = m.id')
      ->where('m.id = ma.media_id')
      ->groupBy('m.id')
      ->orderBy('popularity', 'DESC')
      ->limit(5)
      ->related(['artist', 'album'])
      ->get();

I've managed to find Media::find() in PhalconPHP, but I think that wouldn't really be able to build the same kind ORM of query.

Is there a way to use the querybuilder, and have it return instances of the model? this specific model also uses manytomany relationships, would these be automatically populated, or do I need to provide the data for that in the same query?

I hope my question isn't too vague.



67.0k
Accepted
answer
edited 13d ago

https://docs.phalconphp.com/3.4/en/db-models-relationships

$artistId = 123;
$resultset = $this->modelsManager->createBuilder()
    ->columns([
        'm' => 'm.*',
        'popularity' => 'COUNT(h.id)',
        'played_at' => 'h.played_at',
    ])
    ->addFrom(ShoutzorMedia::class, 'm')
    ->leftJoin(ShoutzorMediaArtist::class, 'm.id = ma.media_id AND ma.artist_id = :artistId:', 'ma')
    ->leftJoin(ShoutzorHistory::class, 'h.media_id = m.id', 'h')
    ->groupBy('m.id')
    ->orderBy('m.id')
    ->limit(5)
    ->getQuery()
    ->execute(['artistId' => $artistId]);

foreach($resultset as $result) {
    /** @var ShoutzorMedia $media */
    $media = $result->m;
    $popularity = $result->popularity;
    $playedAt = $result->played_at;
    // if you have relations set up, you can access them like so:
    /** @var ShoutzorMediaArtist $artist */
    $artist = $media->ShoutzorMediaArtist;
}

Awesome, that really clears things up for me as to how it works in Phalcon. Thanks!

https://docs.phalconphp.com/3.4/en/db-models-relationships

$artistId = 123;
$resultset = $this->modelsManager->createBuilder()
  ->columns([
      'm' => 'm.*',
      'popularity' => 'COUNT(h.id)',
      'played_at' => 'h.played_at',
  ])
  ->addFrom(ShoutzorMedia::class, 'm')
  ->leftJoin(ShoutzorMediaArtist::class, 'm.id = ma.media_id AND ma.artist_id = :artistId:', 'ma')
  ->leftJoin(ShoutzorHistory::class, 'h.media_id = m.id', 'h')
  ->groupBy('m.id')
  ->orderBy('m.id')
  ->limit(5)
  ->getQuery()
  ->execute(['artistId' => $artistId]);

foreach($resultset as $result) {
  /** @var ShoutzorMedia $media */
  $media = $result->m;
  $popularity = $result->popularity;
  $playedAt = $result->played_at;
  // if you have relations set up, you can access them like so:
  /** @var ShoutzorMediaArtist $artist */
  $artist = $media->ShoutzorMediaArtist;
}