List from Joomla 3 / Virtuemart 3 tables

[heypachuco]

I just started working with Fabrik and have run into a problem I cannot figure out.

To start, I'm trying to show a list displaying a few fields from two different mySQL tables. I've created a list for each table, but cannot figure out how to join the two to show the desired fields in one list.

Any help would be greatly appreciated.
Thank you.

 

[cheesegrits]

Is there a "foreign key" on one of the tables? A column that holds a key pointing at the primary key of the row it is related to in the other table?

-- hugh

 

[heypachuco]

No.
I now remember reading about foreign keys in one of the tutorials the other day, but did not understand how to implement one.

 

[cheesegrits]

OK. Foreign keys aren't a "Fabrik thing" they are just a database thing. Obviously if you want to show rows from one table in another, you need to know how the rows relate to each other. So if you are displaying a "parent" table and what to include the "children", the rows in your child table need to have a column that points to the parent they belong to. So you'd have a child.parent_id FK (foreign key) pointing to the parent.id (Primary Key).

Then there's the issue of whether this is a simple one-to-one relationship, so each row in the two tables is only related to one row in the other (husband <-> wife), or one-to-many (mother <-> children).

So do you need one-to-one or one-to-many?

-- hugh

 

[heypachuco]

Thank you for the explanation, and I'm sorry for the delay in my reply.

Both tables include ?virtuemart_product_id? column, so I had thought this could be used to match the data in my attempts to do this myself. Will this be adequate?

I?d like this to be a one-to-one relationship.


Thank you.

 

[cheesegrits]

Hmm, that'll only work if one of the tables is the VM product table, where the 'virtumart_product_id' is the primary key. Our list join code assumes a "Foreign Key points to Primary Key" relationship, so even on one-to-one (non repeat) list joins, one "end" of the join has to be the PK of the table it is on. You can't join FK <-> FK.

-- hugh

 

[heypachuco]

Ok, now I understand this better.
I'll take a look at setting this up in my database.
Thank you.