Complex CC table using 3 models with relations hasmany and hasone

teldrive 26 Aug, 2014

Hi everyone
let me know if it's posible to do a simpler registered joomla users table using CCv5 (we whant to show fields: user, email and groups owned by user). The idea is very simple but very complex to do with CC because joomla has all this data on 3 tables. So to do this on CCv5 we have to use three models
model1=>user
model2=>group
--------------------------user.id hasmany group.user_id this relationship gives an array of values
model 3=>groupname
-------------------------group.group_id hasone groupname.id this relationship gives a single value
[attachment=0]Captura0.JPG[/attachment]
[attachment=1]Captura1.JPG[/attachment]
[attachment=2]Captura2.JPG[/attachment]
[attachment=3]Captura3.JPG[/attachment]

Max_admin 27 Aug, 2014

Hi Teldrive,

Please post the connection's debug!

Regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

teldrive 27 Aug, 2014

Hi Max , thanks by reply,
i get always programing error answer from web, so debugging is not posible yet , using "Split to win concept" i have simplified issue to 2 models (model1 hasmany model2)and later we'll add model 3
let me know if there is any programming error, my doubt is in relationship "hasmany" i think i will receive an array of pair group.user_id , group.group_id, i dont know how CC manage this, when a single value "id" of model1 get several values from model2 how to identify them in table?
[attachment=0]Captura.JPG[/attachment]

teldrive 27 Aug, 2014

also i tested

<?php
return array("group.user_id = user.id");
?>

Max_admin 27 Aug, 2014

When testing a new connection, its better to keep all the "Fields" boxes empty under all models, because this may be causing issues already.

So you have the first 2 models working ? if yes then please post the debug!

Regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

Ro

RobP 27 Aug, 2014

Answer

1 Likes

The user_usergroup.map is called an intermediate table.

In Sql the syntax would be something like this:

USE 'your database'
SELECT u.name, u.username, g.title
FROM `table-prefix_users` u INNER JOIN `table-prefix _usergroup_map' ug
   ON u.id = ug.user_id JOIN `table-prefix _usergroups' g
   ON ug.group_id = g.id  group by u.name

u is the user alias
ug is the user_group_map alias
g is the group alias

Max uses (unfortunately) a slightly different syntax.
The model name is actually the alias.

First model.
The _usergroup_map table hold the foreign keys, it is best to use is as the first model.
Name = map and select table
Table = _usergroup_map

Second model.
For the second model use the users table.
Name = users
Table = _users
Releation = Belongs to
Associated Model = map
Foreign key = user_id
Join type = inner
Primary key = id

Third model
Name = group2 (group is a SQL command, don't use it)
Relation = belong to
Associated Model = map
Foreign key = group_id
Primary key = id

If you put group2.title, users.name in Group in the map model the results wil be nicely grouped.

Front List
Columns list =
group2.title
users.name

Rob

Ro

RobP 27 Aug, 2014

Still it would be great if it was possible to be able to use SQL in Connections.
A field where you can put something in like:

SELECT u.name, u.username, g.title
FROM `table-prefix_users` u INNER JOIN `table-prefix _usergroup_map' ug
   ON u.id = ug.user_id JOIN `table-prefix _usergroups' g
   ON ug.group_id = g.id  group by u.name

is much easier than using the models, especially since you can test it in phpMyAdmin first and then copy it.

Maybe an idea for the next release?

Rob

teldrive 27 Aug, 2014

Hi Rob,
it works like a charm😀 , thanks to share your knowledge, for sure will help a lot of people fans of CF&CC
by the way i will apreciate if you can put an example about hasmany, i tried to use it in this case and spent a lot of time without results
anyway with your solution for me it's better because i can manage groups assigned tousers individually,that's the goal of my customized table
[attachment=0]Captura.JPG[/attachment]

Ro

RobP 27 Aug, 2014

Unfortunately I have no idea.
I have some knowledge of SQL but not much of CC.
The documentation could be better.

Rob

Max_admin 28 Aug, 2014

Hi,

The ouput in the screenshot above could have been achieved by using "hasOne" instead of "hasMany", since an INNER JOIN is used.

I have just built a similar relation from 2 tables, the users table, and the usergroup_map table, first Model User, and 2nd Model Group, relation hasMany, FKey "user_id", that's all!

Under the columns list I have:


User.id
User.username
Group.data

and in the PHP functions box:


Group.data:return implode(",", \GCore\Libs\Arr::getVal($row, array("Group", "[n]", "group_id"), array()));

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

Ro

RobP 29 Aug, 2014

Hi Max,

I would like to know more about the GCore framework,.
Do you know where I could find that information, searched myself but so far without result.

Rob

Max_admin 29 Aug, 2014

Hi Rob,

Unfortunately I couldn't write documentation for this yet, but you can find a helpful example about the db class here:
http://www.chronoengine.com/faqs/70-cfv5/5231-building-complex-db-queries-using-the-gcore-framework.html

However, the full framework has too many classes and helpers.

Regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

Ro

RobP 29 Aug, 2014

Hi Max,

If the GCore framework are the PHP files in cegcore/libs I can see that it will be a bit of a challenge to get it properly documented.

Rob

Forums

Complex CC table using 3 models with relations hasmany and hasone