We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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.



77.7k
Accepted
answer
edited Jul '19

https://docs.phalcon.io/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;
}


1.0k
in reply to Lajos Bencz

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

https://docs.phalcon.io/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;
}