Unable to make cross references between tables

Question

Unable to make cross references between tables

StackyFof May '14

Created May '14	Last Reply May '14	Replies 1	Views 852	Votes 0

StackyFof 925

May '14

Hello dear phalconners,

I am beginner with Phalconphp and I really enjoy the power of this framework. I am trying to code an anti-fraud system for my personnal needs.

Actually, I am here because of a problem which avoiding me to keep coding.

My problem in one sentence: In Orders.php, I would like to perform a restriction on phone (belongs to Clients). I initialized my classes to be bound (like a join in SQL) but the error message shows that there is no link between the two tables. I have some troubles to find out where the problem comes from.

I am unable to make restrictions (implicit clause WHERE) between two joined tables. I will just put how my tables are linked and what are the error messages in order to be understood easily.

Some random details

Tables: clients and orders
Classes: Clients.php and Orders.php
As you can see at the bottom, the method getClients() works properly so the link seems to be good between the two tables.

Table clients:

client_id
phone

Table orders:

order_id
client_id (refers to clients.client_id)

//Clients.php ============================================================

public function initialize(){ $this->hasMany( "client_id", "Orders", "client_id"); $this->hasOne( 'person_id', 'Persons', 'person_id'); }

//Try1 Clients.php ============================================================

$nbOrdersArr = Orders::find( array( "phone LIKE ':phone:'", "bind" => array( "phone" => $this->phone)));

//Try1 Error.log

PHP Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Column 'phone' doesn't belong to any of the selected models (1), when preparing: SELECT [Orders].* FROM [Orders] WHERE phone LIKE ':phone:'' in /home/antifraud/www/api/models/Clients.php:53\nStack trace:\n#0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)\n#1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)\n#2 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)\n#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()\n#4 [internal function]: Phalcon\Mvc\Model\Query->parse()\n#5 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, NULL)\n#6 /home/antifraud/www/api/models/Clients.php(53): Phalcon\Mvc\Model::find(Array)\n#7 /home/antifraud/www/api/index.php(44): Clients->isFraudTel()\n#8 [internal function]: {closure}()\n#9 /home/antifraud/www/api/index.php(325): Phalcon\Mvc\Micro->handle()\n#10 {main}\n thrown in /home/antifraud/www/api/models/Clients.php on line 53

//Try2 Clients.php ============================================================

$nbOrdersArr = Orders::find( array( "clients.phone LIKE ':phone:'", "bind" => array( "phone" => $this->phone)));

//Try2 Error.log

PHP Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Unknown model or alias 'clients' (1), when preparing: SELECT [Orders].* FROM [Orders] WHERE clients.phone LIKE ':phone:'' in /home/antifraud/www/api/models/Clients.php:53\nStack trace:\n#0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)\n#1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)\n#2 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)\n#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()\n#4 [internal function]: Phalcon\Mvc\Model\Query->parse()\n#5 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, NULL)\n#6 /home/antifraud/www/api/models/Clients.php(53): Phalcon\Mvc\Model::find(Array)\n#7 /home/antifraud/www/api/index.php(44): Clients->isFraudTel()\n#8 [internal function]: {closure}()\n#9 /home/antifraud/www/api/index.php(325): Phalcon\Mvc\Micro->handle()\n#10 {main}\n thrown in /home/antifraud/www/api/models/Clients.php on line 53

//Orders.php ============================================================

public function initialize() { $this->hasOne('client_id', 'Clients', 'client_id');

//Return the client Orders.php public function getClient(){ return $this->getClients(); }

I am a little bit lost so any help or idea would be great for me.

Thanks in advance.

Calin Rada · Answer 1 · 2014-05-28T07:06:14-07:00

First of all, you should search clients by phone directly, and then get the orders. But if you really want to search for orders first, then you could do it like this:

$phone = '123456789';

$orders = Orders::find()->filter(function($order){
    if ($order->phone == $phone) {
        return $order;
    }
});