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