Welcome, Guest
Username: Password: Remember me
Forgot your password? Forgot your username? Create an account
Forum
Support forum
Coding inside your component
How to handle custom addjoin() using ORM?

TOPIC: How to handle custom addjoin() using ORM?

How to handle custom addjoin() using ORM? 06 Jan 2017 13:11 #14916

  • liubov
  • liubov's Avatar
  • Offline
  • Elite Member
  • (=) 10 mn and it's ready!
  • Posts: 278
  • Thank you received: 35
  • Karma: 22
Hi cookers,

I am learning the ORM System. It seems easy to custom the queries with ORM for all objects that directly depend on the root Table.

but in a view of my component, i've a custom query using addjoin() with another Table. There are no direct link between the 2 Tables (no FK, no Relations).
In the previous Build (3.0.10) it was easy to add this Join:
	// JOIN LITTER BIRTHDAY = PUPPIES BIRTHDAY
	$this->addJoin('`#__jbreeding_portees` AS _id_liter_ ON _id_liter_.birthday = a.birthday', 'LEFT');

Question :
Is it possible to handle this custom query with ORM ?
Last Edit: 06 Jan 2017 15:00 by liubov.
The administrator has disabled public write access.

How to handle custom addjoin() using ORM? 06 Jan 2017 15:01 #14919

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 984
  • Karma: 140
You query is a bit strange.
Do you want to link only one row ? or multiple ?

In case of only one row (FK), just define the map of the relation in the model constructor
I give you an example for you :
$this->hasOne('id_liter', // name
	'portees', // foreignModelClass
	'birthday', // localKey
	'birthday' // foreignKey
);

Then, you can call your datas. For instance:
$this->orm->select('id_liter.id_etalon.fname');

It gives you an idea.
Note : I fixed some line in the join() function of the ORM class, because the localKey was incorrect. It was using the name of the relation.
It was working for Cook component but not for this specific example.
So upgrade first, and then, try this fork
Coding is now a piece of cake
Last Edit: 06 Jan 2017 15:10 by admin.
The administrator has disabled public write access.

How to handle custom addjoin() using ORM? 06 Jan 2017 15:09 #14920

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 984
  • Karma: 140
If you want the join alone, you can do this
$this->orm->join('id_liter.');
(The point suffix is very important otherwise it will not join)

If you think that you will not reuse this relation, then don't use ORM, and don't create the relation in the model constructor.
ORM is not an obligation, its purpose is to avoid repetitions only.

Another tip:
If you want to load multiple items, then use the logic of N:m, creating a "belongsToMany" relation in the constructor, and then using the 'relation' directive from ORM.

All clear ?
Coding is now a piece of cake
Last Edit: 06 Jan 2017 15:11 by admin.
The administrator has disabled public write access.

How to handle custom addjoin() using ORM? 06 Jan 2017 22:53 #14922

  • liubov
  • liubov's Avatar
  • Offline
  • Elite Member
  • (=) 10 mn and it's ready!
  • Posts: 278
  • Thank you received: 35
  • Karma: 22
The query result is multiple. It displays all availables puppies [Pedigrees] who belong to all actual published litters [Portees].

The join is made on birthday. Here the actual query :
	case 'pedigrees.chiotsavendre':
            
		// BASE FIELDS
		$this->addSelect(	'a.birthday,'
					.	'a.dname,'
					.	'a.id_position,'
					.	'a.id_etalon,'
					.	'a.id_lice,'
					.	'a.id_robe,'
					.	'a.id_sexe,'
					.	'a.id_yeux,'
					.	'a.page,'
					.	'a.photo');

		// SELECT Features
		$this->addSelect('_id_robe_.value AS `_id_robe_value`');
		$this->addSelect('_id_yeux_.value AS `_id_yeux_value`');
		$this->addSelect('_id_position_.value AS `_id_position_value`');
		$this->addSelect('_id_sexe_.value AS `_id_sexe_value`');
   
   		// SELECT Parents names and aliases				
		$this->addSelect('_id_etalon_.falias AS `_id_etalon_falias`');
		$this->addSelect('_id_lice_.malias AS `_id_lice_malias`');
		$this->addSelect('_id_etalon_.fname AS `_id_etalon_fname`');
		$this->addSelect('_id_lice_.mname AS `_id_lice_mname`');
                
   		// SELECT View Access	
		$this->addSelect('_access_.title AS `_access_title`');

		// JOIN FK VALUES
		$this->addJoin('`#__jbreeding_etalons` AS _id_etalon_ ON _id_etalon_.id = a.id_etalon', 'LEFT');
		$this->addJoin('`#__jbreeding_lices` AS _id_lice_ ON _id_lice_.id = a.id_lice', 'LEFT');
		$this->addJoin('`#__jbreeding_sexes` AS _id_sexe_ ON _id_sexe_.id = a.id_sexe', 'LEFT');
		$this->addJoin('`#__jbreeding_robes` AS _id_robe_ ON _id_robe_.id = a.id_robe', 'LEFT');
		$this->addJoin('`#__jbreeding_yeuxs` AS _id_yeux_ ON _id_yeux_.id = a.id_yeux', 'LEFT');
		$this->addJoin('`#__jbreeding_statuts` AS _id_position_ ON _id_position_.id = a.id_position', 'LEFT');
				
                // JOIN LITTER BIRTHDAY = PUPPIES BIRTHDAY
		$this->addJoin('`#__jbreeding_portees` AS _id_liter_ ON _id_liter_.birthday = a.birthday', 'LEFT');
               
                // JOIN VIEW ACCESS LEVEL
                $this->addJoin('`#__viewlevels` AS _access_ ON _access_.id = a.access', 'LEFT');
                
                // WHERE - FILTER :Only display the Published Litters and Published Puppies
		$this->addWhere("a.published = 1");
		$this->addWhere("_id_liter_.published = 1");

I able to handle all the query using ORM except this JOIN on Birthday field ...
The administrator has disabled public write access.

How to handle custom addjoin() using ORM? 07 Jan 2017 00:20 #14923

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 984
  • Karma: 140
Ok, it is what I understood.
In reallity, this relation can be understood as 1:1 because there is always only one portee for pedigree (with a unique the etalon/lice combinaison)
So the code I gave to you is correct.

You can even add this for filtering the published access of portee:
$this->orm->access('id_liter', array(
    'publish' => 'published',
));
But this published access takes in consideration the ACL, so you may not want to use it.


Otherwise, another possibility for playing with ORM
$this->orm->filter('id_liter.published', array(
    'value' => 1,
));
Coding is now a piece of cake
Last Edit: 07 Jan 2017 00:23 by admin.
The administrator has disabled public write access.

How to handle custom addjoin() using ORM? 07 Jan 2017 10:54 #14924

  • liubov
  • liubov's Avatar
  • Offline
  • Elite Member
  • (=) 10 mn and it's ready!
  • Posts: 278
  • Thank you received: 35
  • Karma: 22
Ok thanks a lot for your help :)

But i think will try with the "belongsToMany" relation, because most of time, I've several litters [ie : 5], so the view 'Chiots à Vendre' displays all the puppies from all these Actual Litters.
In that case the Join on Birthday is enough, with Published, Access check, both side.

The only trouble I 've in your examples, is that I do not have FK's as used in a normal Relation Table ( ie: Pivot Table 'Visits' ).
I have no Table that consolidate Ids [id_pedigrees, id_litter] ...
But I will study that and try ...
Last Edit: 07 Jan 2017 10:56 by liubov.
The administrator has disabled public write access.

[Closed] How to handle custom addjoin() using ORM? 10 Jan 2017 17:21 #14933

  • liubov
  • liubov's Avatar
  • Offline
  • Elite Member
  • (=) 10 mn and it's ready!
  • Posts: 278
  • Thank you received: 35
  • Karma: 22
Resolved with the Exploded prepareQuery() - ORM reduced

A very nice stage before FULL ORM :)
The administrator has disabled public write access.
Forum
Support forum
Coding inside your component
How to handle custom addjoin() using ORM?
Time to create page: 0.102 seconds

Get Started

Log In Sign In