• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Database join

nofc_fro

Member
Hi,
I have a few cases to ask, forgive me :)

CASE 1. Database join in "ccc" list
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, name (e.g. 100, frank)
The "bbb" table has three elements: id, aaa_id, surname (e.g. 201.100, smith)

I create a new "ccc" list that has two elements: id,namesurname(database join)
I would like to have this result: 999,frank smith

If I create an Element with Plug-in "Databasejoin" I go to the tabs "Data", I select the table "aaa", Value "id" and in "Concat label" what should I do?

CASE 2. Database join in "bbb" list
I have a similar situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, league,num (e.g. 100, nhl, 326)
The "bbb" table has three elements: id, month_id, name_month (e.g. 200, 326, april)
I would like to create a new "database join" element in the "aaa" table that gives me the name of the month
How can I do?

CASE 3. One to many
This is the most difficult case.
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, company (e.g. 100, corporationxxx)
The "bbb" table has three elements: id, company_id, attribute_id, value. BUT I have more records with aaa_id. E.g.->
"aaa" table
100,corporationxxx
200,corporationyyy

"bbb" table
1,100,10,sun
2,100,11,happy
3,100,12,sing
4,200,10,snow
5,200,11,sad
6,200,12,silence
I would like to create a new "database join" elements in the "aaa" table that gives me the list of values:
NEW "aaa" table 100,corporationxxx,sun,happy,sing
How can I do?
 
Let's deal with them one by one ...

CASE 1. Database join in "ccc" list
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, name (e.g. 100, frank)
The "bbb" table has three elements: id, aaa_id, surname (e.g. 201.100, smith)

I create a new "ccc" list that has two elements: id,namesurname(database join)
I would like to have this result: 999,frank smith

If I create an Element with Plug-in "Databasejoin" I go to the tabs "Data", I select the table "aaa", Value "id" and in "Concat label" what should I do?

You can use dependent subqueries in your concat, which you'll have to do to get the 'surname' from bbb

Code:
{thistable}.name, ' ', (SELECT surname FROM bbb WHERE aaa_id = {thistable}.id)

Let me know if that works, and we'll move on to the second one.

-- hugh
 
No, I have no error but I have a empty field :(
test.jpg
 
Last edited:
What are you expecting to see in the list view of CCC?

If you add a new record in CCC, you should then have the "Join" element, which allows you to select from AAA, with the concatenated label as the dropdown labels. So on form CCC you are choosing a record from AAA, with the name from BBB concatenated to the label.

It sounds like you may be confusing the database join element with list joins. Are you trying to create a table that shows all the records of AAA and BBB joined, so you see first and last names?

If so, then edit List AAA, go to the Data tab, Joins, and add a join ...

From table: aaa
To table: bbb
From element: aaa.id
To element: bbb.aaa_id

Repeated: No

... and save.

That will then add a new group to AAA for the records from BBB, and list AAA will show the joined groups as individual rows.

-- hugh
 
I always have two tables:
"aaa" -> id, name (eg 100, frank; 101, peter)
"ddd" -> id; aaa_id; topic_id; topic (1,100,10, metal; 2,100,20, yellow; 3,101,10, pop; 4,101,20, armony)
ddd.jpg


in the table "aaa" I have to add two new "database join" elements with the "where" condition.
The first element will be called "Music" and will have the condition that id_topic = 10;
The second element will be called "Genre" and will have the condition that id_topic = 20;
How can I do?
 
Hi,
I want to merge the "aaa" table with the "ddd" table, in particular I want to create an element in "aaa" that applies a condition WHERE on the "ddd" table.

where.png
 
Last edited:
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top