[SOLVED] Joining table then primary list shows records only if record in joined table exists

[pastvne]

Hi All:

I have a primary list
I populate it with a public data source ( a CSV list of airports)
Details about the airports change over time so we need to be able to clear the primary table and re-load it periodically.
I'm allowing the standard 'id' column produced by fabrik with auto-increment but the real reference is another column called loc_id which contains the airport code (e.g., APA)


Joined table
I created a second list to store additional data not provided in the public data source
I also allow Fabrik to create its own id column, auto increment
I store a loc_id in this table as well (e.g., APA)

I INNER join the second table to the primary table from primary.loc_id to secondary.loc_id

This seems to work and the elements display properly.
However, in the primary list display, records display ONLY if there is a corresponding record in the joined table.

I am joining based on loc_id because when we re-load the primary table, there could be new airports or some could be deleted and this would change the id in the primary table. So I need a value for joining that will not change when airports are re-loaded (so that the second list data persists and reconnects with the appropriate parent record).

My Questions:
Is there a better way to model this?

Do I need to use the implicit 'id' column generated by fabrik as my primary key for the primary table or can I use the loc_id as my primary key? If so, do I need to make this change at the database level? How do I ensure that I don't break fabrik?

 

[troester]

1. In general: In Fabrik one leg of the join must be a primary key (joining some other unique columns may work if your list is strictly readonly)

2.

However, in the primary list display, records display ONLY if there is a corresponding record in the joined table.

This is how a INNNER join is defined. Use a LEFT join to display also records without a corresponding record.

 

[pastvne]

Thanks Troester. I tried left and MYSQL filled up my /tmp directory. I made sure loc_id was indexed on both lists. I suspect that the root issue is that I'm joining two non-primary keys. Would it help, at the database level, if I marked these columns as unique, which they are?

 

[troester]

if I marked these columns as unique

No, if data is written to your joined list Fabrik will assume a primary key on one site.

MYSQL filled up my /tmp directory

I don't understand why MYSQL should write to any file?

 

[pastvne]

I think I have it working and made two mistakes which I have corrected:

1) I didn't index loc_id in other table. I think this was causing MySQL to create some temp table it was using which filled up /tmp. It had a bunch of large files with MYSQL in the name. If I bounced MySQL to get the server back, the large files in /tmp disappeared. So, I think I was triggering some sort of temp table build caused by my lack of understanding.
2) I hadn't marked the loc_id columns, which are unique, as Unique. After I did this, the list worked and is actually performing well. I suppose now, for safety, I should download the IS_UNIQUE fabrik validation plug-in and apply that to the loc_id field to reduce the chance that I might submit multiple records with the same loc_id into the second table.

As always Troester, your patient help is something I'm thankful for on each interaction. Thank you for all you do!