• 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.

Cross references within one table?

Jaanus

Super Moderator
Hi all,
does anyone have an idea how to achieve this (if it's possible at all with Fabrik):

Let's say I have a table for facts, terms (etc) named facts (id, fact, description) and a list for it.
When writing or editing a fact I want sometimes to add one or many references to other fact(s). But so that another reference to the current fact is also created for another fact on the fly.

Most probably I have an additional table for such references. Say it's name is facts_related (id, facts_id, related_id) and I have a repeated list join for facts list (facts->facts_related, id->facts_id).
Facts (id, fact) are
1, fact 1
2, fact 2
3, fact 3
4, fact 4

and let's say fact 4 refers to all the rest in the facts_related table:
facts_id=4 related_id=1;
facts_id=4 related_id=2;
facts_id=4 related_id=3;

But in this case I have also to edit each other facts one by one if I want to make them refer back to fact 4 so each one have joined data:
facts_id=1 related_id=4;
facts_id=2 related_id=4;
facts_id=3 related_id=4;

It seems I need either
  1. an autocreation of an additional joined data
    • when I create fact 4 and add joined data facts_id=4 related_id=1
      then also acts_id=1 related_id=4 is created in background
  2. or perhaps an "enhanced" ON clause that would work only in readonly mode (list and details)
    • SELECT * FROM facts LEFT JOIN facts_related ON facts_related.facts_id = facts.id OR facts_related.related_id = facts.id
or what other people think?
 
Found a (temporary?) solution. This is implementable ATM with dbjoin or cdd multiselect mode.
  1. First you create an usual dbj/cdd element (say facts_related) for the list (say facts) pointing to the same dbtable id and set it to multiselect or checkbox. A new dbtable facts_repeat_facts_related is created. Set it accessible only in form view.
  2. Then you go to database (phpmyadmin or whatever) and create a view dbtable facts_repeat_facts_related_all as:
    Code:
    SELECT CONCAT(1,id) as id, parent_id, facts_related as facts_related_all, params FROM facts_repeat_facts_related
    UNION
    SELECT CONCAT(2,id) as id, facts_related, parent_id, params FROM facts_repeat_facts_related
    These concats are made "just in case" to ensure the id has always an unique value.
  3. And finally you create another multi dbjoin/cdd named facts_related_all with the same params like facts_related - except accessibility - set it to be accessible only in details view and if needed, also in list view.
There is also an issue when using F3.4.3 (no idea yet how with the latest github from master branch). The dbjoin multi element is still visible in form view although its access there is set to nobody - and it tries to submit data (then error msg says that I tried to submit data to db view that is not insertable into).
 
Hi all,
does anyone have an idea how to achieve this (if it's possible at all with Fabrik):

Let's say I have a table for facts, terms (etc) named facts (id, fact, description) and a list for it.
When writing or editing a fact I want sometimes to add one or many references to other fact(s). But so that another reference to the current fact is also created for another fact on the fly.

Most probably I have an additional table for such references. Say it's name is facts_related (id, facts_id, related_id) and I have a repeated list join for facts list (facts->facts_related, id->facts_id).
Facts (id, fact) are
1, fact 1
2, fact 2
3, fact 3
4, fact 4

and let's say fact 4 refers to all the rest in the facts_related table:
facts_id=4 related_id=1;
facts_id=4 related_id=2;
facts_id=4 related_id=3;

But in this case I have also to edit each other facts one by one if I want to make them refer back to fact 4 so each one have joined data:
facts_id=1 related_id=4;
facts_id=2 related_id=4;
facts_id=3 related_id=4;

It seems I need either
  1. an autocreation of an additional joined data
    • when I create fact 4 and add joined data facts_id=4 related_id=1
      then also acts_id=1 related_id=4 is created in background
  2. or perhaps an "enhanced" ON clause that would work only in readonly mode (list and details)
    • SELECT * FROM facts LEFT JOIN facts_related ON facts_related.facts_id = facts.id OR facts_related.related_id = facts.id
or what other people think?
I?m imagine doing this in a different way, maybe i?m wrong. This is my approach; use the element multi select dropdown (or checkbox) "facts_related" pointing into the same list, and add a php form plugin (onAfterProcess) where you can update only the "facts_related" of the chosen rows in that dropdown to append the value from the actual row (being created or edited).
 
Indeed, this one seems to be working for me
PHP:
$related = $data['2016_facts___related_data_raw'];
$row = $data['rowid'];

$db = JFactory::getDbo();

foreach($related as $rel)
{
$query = $db->getQuery(true);
$query->select('id')->from('2016_facts_repeat_related_data')->where('parent_id = ' . $db->quote($rel) . 'AND related_data = ' . $db->quote($row));
$db->setQuery($query);
$res = $db->loadObjectList();
if (count($res) == 0)
{
    $query->insert('2016_facts_repeat_related_data')->set('parent_id = ' . $db->quote($rel))
->set('related_data = ' . $db->quote($row));
    $db->setQuery($query);
    $db->execute();
  }
}
 
When we edit data and deselect things in multidbjoin we need also another php plugin. Tested this but it seems that it doesn't work:

PHP:
$related = $data['2016_facts___related_data_raw'];
$row = $data['rowid'];
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('r1.id AS id1, r2.id AS id2')
      ->from('`2016_facts_repeat_related_data` AS r1 LEFT join `2016_facts_repeat_related_data` AS r2 ON r2.parent_id = r1.related_data')
      ->where('r1.related_data = ' . $row . ' AND r2.id IS NULL');
$db->setQuery($query);
$rows = $db->loadObjectList();
if (count($rows) > 0)
{
foreach($rows as $r)
  {
    $query->delete('2016_facts_repeat_related_data')
          ->where('id = ' . $db->quote($r->id1));
    $db->setQuery($query);
    $db->execute();
  }
}
 
Last edited:
Have you tried with select replace instead of delete? Adding a relationship means insert the current-item-id of the current item in one array like this

["another-id","current-item-id"]

And if you want to delete that relationship, you couldn?t delete, because in that way you will lose another relationships that the item have.

Instead, using SELECT REPLACE to convert that "current-item-id" to nothing, and the field sintaxis will be preserved.
 
My goal is exactly that
if I delete where parent_id = {rowid} and related_data = x
I would delete also where parent_id = x and related_data = {rowid}
That means a relationship between rows is either 2-sided or it doesn't exist at all
 
but take this in consideration. for example, lets say that we have 5 facts.

fact 1
id:1
related_facts:["2","3"]

fact2
id:2
related_facts:["1","5"]

fact 3
id:3
felated_facts:["1"]

fact 4
id:4
related_facts:"null"

fact 5
id:5
related_facts["2"]

so, if you want to delete the relationship between 1 and 2, you can?t simply delete all the related fact info, because in that scenario you will lose the relationship between 1 and 3, and between 2 and 5.

instead of that you better make a select replace. for that you have to take in care of 4 cases:

1st - when only one id exists in the related_facts field, like this

fact6
id:6
related_facts:7

fact7
id:7
related_facts:6

In that case, you can simply delete.

2nd- when you have more than one id stored, and the id that you want to delete is the first of the array, i.e. when you are editing the fact 1 and you want to delete his relationship with fact 2. you will need to perform a select replace from "id", (including the colon) to empty string.

fact 1
id:1
related_facts:["2","3"]

fact2
id:2
related_facts:["1","5"]

3rd - when the id to delete is in the middle the same replace of the 2nd case could be applied taking the same effect.

4th - when the id to delete is the last one. in that case the propper replace can be from ,"id" to empty string.
 
Last edited:
My goal is exactly that
if I delete where parent_id = {rowid} and related_data = x
I would delete also where parent_id = x and related_data = {rowid}
That means a relationship between rows is either 2-sided or it doesn't exist at all
I?m thinking that one of them are performed automatically at the moment that you unselect in the multiselect dropdown and save and, the only delete that you need to perform using the php plugin is the replace of the strings like i?ve tried to explain in another post.

Also at this moment, i?m just realize that you will need a new calc element to perform a comparison between the value stored in the field before the edition and the value after the edition, just to know which related facts was unselected in the edition and where do you need to perform the replace-or-delete action.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top