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

Database join attributes

So, so I understand that phalcon doesnt handle joins like other framewoks. But I would still like to format my data like that for ease of development.

When querying for all the servers of a single user, Phalcon returns multiple of the same user for each server, I would like to format it so when I have 1 that user contains all the servers they have. Like so:

Object Users extends Phalcon\Mvc\Model (
  ->id (public) = Numeric string (1) "1"
  ->username (public) = String (5) "admin"
  ->ip_address (public) = String (9) "127.0.0.1"
  ->first_name (public) = String (3) "The"
  ->last_name (public) = String (5) "Admin"
  ->premium (public) = Numeric string (1) "1"
  ->suspended (public) = Numeric string (1) "0"
  ->suspension_expiry (public) = NULL
  ->suspension_reason (public) = NULL
  ->created_at (public) = String (19) "2017-09-26 21:47:20"
  ->updated_at (public) = String (19) "2017-09-26 21:47:20"
  ->servers (public) = Object Phalcon\Mvc\Model\Resultset\Complex extends Phalcon\Mvc\Model\Resultset (
    Object Servers extends Phalcon\Mvc\Model (
      ->id (public) = Numeric string (1) "1"
      ->owned_by (public) = Numeric string (1) "1"
      ->posted_by (public) = Numeric string (1) "2"
      ->type (public) = Numeric string (1) "1"
      ->ip (public) = String (13) "151.80.109.86"
      ->port (public) = Numeric string (4) "9987"
      ->query_port (public) = Numeric string (5) "10011"
      ->players (public) = NULL
      ->max_players (public) = Numeric string (3) "512"
      ->description (public) = NULL
      ->map (public) = NULL
      ->unlisted (public) = Numeric string (1) "0"
      ->created_at (public) = String (19) "2017-09-26 21:47:21"
      ->updated_at (public) = String (19) "2017-09-26 21:47:21"
    )
    Object Servers extends Phalcon\Mvc\Model (
      ->id (public) = Numeric string (1) "2"
      ->owned_by (public) = Numeric string (1) "1"
      ->posted_by (public) = Numeric string (1) "1"
      ->type (public) = Numeric string (1) "2"
      ->ip (public) = String (13) "151.80.109.86"
      ->port (public) = Numeric string (5) "27015"
      ->query_port (public) = Numeric string (5) "27015"
      ->players (public) = NULL
      ->max_players (public) = Numeric string (3) "512"
      ->description (public) = NULL
      ->map (public) = NULL
      ->unlisted (public) = Numeric string (1) "0"
      ->created_at (public) = String (19) "2017-09-26 21:47:21"
      ->updated_at (public) = String (19) "2017-09-26 21:47:21"
  )
)

How can I acheive this with phalcon?

Im trying to acheive somelike how how Doctrine and Eloquent handle joins, Where the joined table is a child container (resultset in phalcons case) instead of multiple of the same user with diffrent joined servers.

edited Sep '17

Update, I have managed to do this

foreach ($items->toArray() as $item) {
    $servers[] = $item->servers;
}

$return = $items[0]->users;
$return->writeAttribute('servers', $servers);
return $return;

But its not something Im pleased about tbh

edited Sep '17

This should retain your Objects.

Yeah, the toArray is something I prefer to avoid otherwise you get an entire object dump even with a simple result.

Here's something to try, I put this in the overloaded Users::findFirst method, yet you can put it anywhere:

  • So I have a model for: Users, and Servers.

models/Servers.php

    <?php
    public function initialize()
    {
         $this->hasMany('owned_by', 'Users', 'id', ['alias' => 'Users']);
         $this->belongsTo('owned_by', '\Users', 'id', ['alias' => 'Users']);
    }

models/Users.php

    <?php
    // .... Users model
    /**
     * Allows to query the first record that match the specified conditions
     *
     * @param mixed $parameters
     * @return Users|\Phalcon\Mvc\Model\ResultInterface
     */
    public static function findFirst($parameters = null)
    {
        $result = parent::findFirst($parameters);
        $result->server_list = \Servers::findFirstByOwnedBy($result->id);

        return $result;
    }

In the ^^ Above ^^ Example, you could just apply to $result->ANY_VAR_NAME -- Yet I feel like there is a better way to do this.

IndexController.php (This is for testing)

    <?php

    class IndexController extends ControllerBase
    {

        public function indexAction()
        {
            $user = \Users::findFirstById(1);

            echo '<h1>user_id: ' . $user->id . '</h1><br>';
            foreach ($user->server_list as $instance) {
                foreach ($instance as $key => $value) {
                    if ($key == 'id') {
                        $key = 'server_id'; // just so its more readable
                    }
                    echo "$key = $value <br>";
                }
                echo '<hr>';
            }

            die;
        }
    }

My results with some fake data is:

user_id: 1

server_id = 1 owned_by = 1 name = foo

server_id = 2 owned_by = 1 name = fizz

server_id = 3 owned_by = 1 name = fun

edited Sep '17

This should retain your Objects.

Yeah, the toArray is something I prefer to avoid otherwise you get an entire object dump even with a simple result.

Here's something to try, I put this in the overloaded Users::findFirst method, yet you can put it anywhere:

  • So I have a model for: Users, and Servers.

models/Servers.php

   <?php
   public function initialize()
   {
        $this->hasMany('owned_by', 'Users', 'id', ['alias' => 'Users']);
        $this->belongsTo('owned_by', '\Users', 'id', ['alias' => 'Users']);
   }

models/Users.php

   <?php
  // .... Users model
   /**
    * Allows to query the first record that match the specified conditions
    *
    * @param mixed $parameters
    * @return Users|\Phalcon\Mvc\Model\ResultInterface
    */
   public static function findFirst($parameters = null)
   {
       $result = parent::findFirst($parameters);
       $result->server_list = \Servers::findFirstByOwnedBy($result->id);

       return $result;
   }

In the ^^ Above ^^ Example, you could just apply to $result->ANY_VAR_NAME -- Yet I feel like there is a better way to do this.

IndexController.php (This is for testing)

   <?php

   class IndexController extends ControllerBase
   {

       public function indexAction()
       {
           $user = \Users::findFirstById(1);

           echo '<h1>user_id: ' . $user->id . '</h1><br>';
           foreach ($user->server_list as $instance) {
               foreach ($instance as $key => $value) {
                   if ($key == 'id') {
                       $key = 'server_id'; // just so its more readable
                   }
                   echo "$key = $value <br>";
               }
               echo '<hr>';
           }

           die;
       }
   }

My results with some fake data is:

user_id: 1

server_id = 1 owned_by = 1 name = foo

server_id = 2 owned_by = 1 name = fizz

server_id = 3 owned_by = 1 name = fun

While that solves the problem for some, I am joining the tabel in a PHQL

$items = self::query()->createBuilder()
            ->columns(["Users.*", "Servers.*"])
            ->where("Users.id = :userid:", ['userid' => $userid], ['userid' => Column::TYPE_INTEGER])
            ->innerJoin("Servers", "Users.id = Servers.owned_by", "Servers")
            ->getQuery()->execute();

Because what i am working will need to keep load oft he database. And there are expected to be more joins on top, for instance each server has a oneToOne type and then then another oneToMany of all the users on the server.

So I would need

$user // a single server owner
$user->servers // a collection of all the servers owned by the user
$user->servers[0]->type // a single servers type
$user->servers[0]->users // a collection of the all the users on the server.
$user->servers[0]->users[0]->roles // a collection of the specific users role on the server

By the default behavour of the ORM returning 1 user for each joined row, thats alot of duplicate data and spegetti code to achieve the task

Posting the (relevant) source of your model class could help... Also, which version of PHP + Phalcon are you running? soundcloud downloader apk

Nice post, thank you so much.