Duplicate records created by inline edits

[p38]

Hi,

My clients have been complaining that duplicate records occasionally occur when they edit data using the inline edit facility.

I have also noticed this, but thought it was perhaps my dev machine.

Is there any possibility that this can happen using inline edits?

Do you at any stage when editing, check and insert a new record?

Looking at the raw data I see the fabrik list adds a duplicate record in the list display, altho there is only 1 row in the raw database.

So my question is how does this happen? How can I view the SQL query than fabrik creates in order to display the list?

This is an urgent problem, and needs to be addressed ASAP please, I need to give feedback to my clients.

Paul.

 

[cheesegrits]

If I'm understanding you correctly, you are saying that there is no duplication in the underlying table, that the duplication is just happening in the List display?

Do the duplicates still show when you reload the list page, or only when first doing the inline edit?

Does you list have any joins?

-- hugh

 

[p38]

Yes, it seems the underlying table is correct, only duplicate records in list view, and only occurs occasionally when using inline edit.

A few of the elements do make use of database joins, but I cannot see how this would influence the underlying list SQL

How can I see what the underlying SQL script is for this list, I guess this will quickly tell me where the problem is, unless using inline edits does funny things when rendering the grid.

Paul.

 

[rob]

Does the list render correctly on page load, and then after using the inline edit do the duplicate rows appear, or does it render incorrectly on page load.

If its on page load you can add '&fabrikdebug=1' to the page's url and you should see a load of debug headings appear which you can click on to see various aspects of the code we're running. One of them is the db query that we use to build the list.

I think Hugh was getting at list databse joins, which can make the data appear to be duplicated when you have a one to many relationship. So one school with lots of children would show a row for each child with the school info duplication. To stop that you would turn on the join's merge data option

Is there a URL we can look at?

 

[p38]

Ok I think I found your problem.

I use database dropdown list on an element, and the lookup database can have the same names, but different ID's.

You do a left join in order to show the CONCATED labels in the list view.

However, you do not inlcude the WHERE clause of the DB join. This is why duplicate rows are being shown.

In my setup I have a where {$my-ID} clause which shows me a dropdown of only the items I need for current user.

Paul.

Below is SQL, and attached image is settings with the where clause.

SELECT SQL_CALC_FOUND_ROWS DISTINCT `teeoff`.`id` AS `teeoff___id`, `teeoff`.`id` AS `teeoff___id_raw`, `teeoff`.`date_time` AS `teeoff___date_time`, `teeoff`.`date_time` AS `teeoff___date_time_raw`, `teeoff`.`tournament` AS `teeoff___tournament_raw`, `tournament`.`name` AS `teeoff___tournament`, `teeoff`.`teeoff_date` AS `teeoff___teeoff_date`, `teeoff`.`teeoff_date` AS `teeoff___teeoff_date_raw`, `teeoff`.`teeoff_time` AS `teeoff___teeoff_time`, `teeoff`.`teeoff_time` AS `teeoff___teeoff_time_raw`, `teeoff`.`player1` AS `teeoff___player1_raw`, CONCAT(players.name, ' (',players.handicap,')' ) AS `teeoff___player1`, `teeoff`.`player2` AS `teeoff___player2_raw`, CONCAT(players_0.name, ' (',players_0.handicap,')' ) AS `teeoff___player2`, `teeoff`.`player3` AS `teeoff___player3_raw`, CONCAT(players_1.name, ' (',players_1.handicap,')' ) AS `teeoff___player3`, `teeoff`.`player4` AS `teeoff___player4_raw`, CONCAT(players_2.name, ' (',players_2.handicap,')' ) AS `teeoff___player4`, `teeoff`.`username` AS `teeoff___username_raw`, `teeoff`.`username` AS `teeoff___username`, `teeoff`.`team_name` AS `teeoff___team_name`, `teeoff`.`team_name` AS `teeoff___team_name_raw`, `teeoff`.`starting_tee` AS `teeoff___starting_tee`, `teeoff`.`starting_tee` AS `teeoff___starting_tee_raw`, `teeoff`.`id` AS slug , `teeoff`.`id` AS `__pk_val` FROM `teeoff` LEFT JOIN `tournament` AS `tournament` ON `tournament`.`name` = `teeoff`.`tournament` LEFT JOIN `players` AS `players` ON `players`.`name` = `teeoff`.`player1` LEFT JOIN `players` AS `players_0` ON `players_0`.`name` = `teeoff`.`player2` LEFT JOIN `players` AS `players_1` ON `players_1`.`name` = `teeoff`.`player3` LEFT JOIN `players` AS `players_2` ON `players_2`.`name` = `teeoff`.`player4` WHERE ( teeoff.username = '46' AND teeoff.tournament IN (SELECT name from tournament WHERE tournament_active = 'yes') ) ORDER BY `tournament`.`name` desc, `teeoff`.`teeoff_time` desc,`teeoff`.`teeoff_time` ASC

 

[p38]

friendly bump,

this is an urgent fix please.

Paul

 

[rob]

Im kind of confused as to why that shows additional rows.

The where statement on the database join element is there to limit the choices the user can select in the form, I wouldn't expect to use it on the list view as I can see that causes unexpected results.

Is it not possible to set prefilters on the list to show the users data? That's how traditionally I would expect it to be done.

 

[p38]

your "where" statement pertains to the pre-filter clause of the list.

The problem is you use left joins to show the concated labels, but you need to also include the where statement of the concated label in the left join, otherwise you will get duplicate records.

eg:

list fieldname : division_desciption (databasejoin element)

lookup table "Divisiontable" as 2 fields, userid and description. To display current user records, we use where statement "username = {$my->id}".

Now to display the list records, which includes conacated label, you do a left join on "division_desciption = divisiontable.description".

The problem is, what happens if "divisiontable" has 2 or more records with same description, but 2 different userid's?

You will then get a duplicate record.

Please note: I can forsee a problem if you inlude the where/order by clause in your left outer join, as you will most probably have to split the where and order by clause.

Paul.

 

[cheesegrits]

I'm sorry, I'm horribly confused. You said:

you do a left join on "division_desciption = divisiontable.description"

... but I don't see any reference to divisiontable in the query you pasted in your previous post.

-- hugh

 

[p38]

my description example does not refer to the sql script, it is merely an attempt to describe plainly what is happening.

In other words, if you do a database join on a lookup list that has duplicate records, your resultant list will also have duplicate records, unless you apply a "where" clause in your left join.

Paul.

 

[rob]

But the issue I see is that including the where will incorrectly filter the data and cause other issues for sure.

I logged into the site and from the description i presume i should look at the teeoff times list, but my test tournament is not active, so I can't see any tee offs that I make.
Judging from the url is this the live site (I'm sure you've told me this but I can't remember)?
Can I activate my tournament 'Robs tournament' to test, or is this going to cause issues for your users?

Cheers
Rob

 

[p38]

Yes the site is live, but if you login under rob you can xperiment no prokem.

I have activated a tournament.

There are 2 players in the player list, both the same name, but different handicaps and different userid's. Yours is 43.

If you see teeoff times, there are only actually 2 teeoff records under your name, but the list view shows multiple records, inlcuing doing a join for the other player user id.

if you allowed a where clause in the left join, I am sure this will solve it.

eg:

LEFT JOIN (`players` AS `players` ON `players`.`name` = `teeoff`.`player1` where `players`.username = 43)

Paul.

 

[rob]

Ok I see where the issue is, and I don't think its actually in the query we are building per see.
Yes, if we add that in it will fix your query, but......

You have set the player database join's to use the user name as the value, and what I think is happening is that your system has more than one player with the same name.

The stored value be the primary key for the players table (the id field), this would then produce a unique reference and thus avoid the duplication of records you are seeing in your tables.

What would happen if someone's name change (e.g. someone gets married) - the way you have things set up now would mean that any tee off times reference would be lost for that player - probably not a good thing (tm)

So the best thing to do would be to edit those player elements and set the value option to use the 'id' field.

You would also have to go into via phpmyadmin and then manually alter any previously stored data for the tee off time players, replacing their name with their id.

 

[p38]

Hi, there is a reason I am not using the ID, due to system design and needs.

You will see in the lookup databsejoin, I solve the duplicate name problem by issuing a "where username = {$my-id}".

Each username will have unique names, altho it is possible that duplicate names exist in the database, but never within a username.

The problem with fabrik is that when you display your concat labels, you issue a left join, but my point is you also have to take into account the "where" statement of the databasejoin as well, as this will prevent duplicate records.

Note this only occurs with concat labels, as this is the only time I assume you use a left join.

As mentioned, I cannot use an ID, as the system design precludes this, and negates the functionality of a lookup list.

Paul.

PS: Using names instead of ID's keeps historical data. If someone gets married and changes name, and you use an ID, then you lose the historical entry of what the teeoff was at that point in time. At least if you store the actual name, and the name is changed, you still have the name it was when captured.

 

[cheesegrits]

I'm with Rob on this one. The 'value' for a join element rendered as a dropdown or radio has to be unique. It's a one-to-one join.

The "WHERE" query option we provide in the join element is purely to allow you to be more specific with the selection options in a form view. So in your situation, maybe restricting the options to just players participating in a given tournament.

The 'WHERE' clause is NOT designed to individualize a non-unique join selection when rendering an option that has already been chosen.

This isn't going to change, as (like Rob) I can already think of several situations where including the join element's WHERE clause in our main getData() list query would have potentially nasty side effects, especially in our merge code.

-- hugh

 

[cheesegrits]

I've modified the en-GB language for the 'value' tooltip, to make it clear that this value must be unique in the joined table, and would usually be the PK.

-- hugh

 

[cheesegrits]

BTW, re:

As mentioned, I cannot use an ID, as the system design precludes this, and negates the functionality of a lookup list.

If you can explain why your current design precludes using the PK of the joined table as the 'value', we can probably come up with a design that would accommodate this.

-- hugh

 

[p38]

BTW, re:
If you can explain why your current design precludes using the PK of the joined table as the 'value', we can probably come up with a design that would accommodate this.
-- hugh

The whole point of lookup lists is based on the fact that you merely want to prevent the user from entering values that can cause spelling mistakes, and erroneous data capture. This concept is used all over in database design and enables data integrity.

So there is no point in having an ID field in the resultant database, cause then you have to provide views in order to reflect the chosen value. And if your lookup list contains for example, "tea", "coffee", "milk" etc etc, what point is there having an ID to reflect the chosn value.

Actually, in some web hosting sites, they do not allow mySQL views, cause if created incorrectly, the data slows servers down etc etc. So in this case, one is stuck.

Now my point is, if you have a different set of lookup lists for each user, for example, it could happen that duplicate values are available. This is solved by your facility of a "where" clause so the the correct list is displayed, by virtue of , eg: "userid"

However, when you have a concatenated label, the only way fabrik can show the label chosen, is to left join from the databasejoin list. This works fine if the lookup list has unique values.

But as it so happens, in my case, it does not have unique values, as my lookup list caters for mutiple users.

So in order to solve your problem, you simply need to add the supplied "where" clause into the left join statement.

I cannot see how this could create other problems in fabrik, cause all you need to do is wherever the left join statement is created, you just need to add the supplied where clause of the concacted label.

As mentioned: This is only a problem when using concated labels, as this is when you use a left join.

I trust this has been a good enough explanation and motivation of my problem.

Paul

 

[rob]

Perhaps your concept of what is happening under the hood is not aligned with what actually happens.

So there is no point in having an ID field in the resultant database, cause then you have to provide views in order to reflect the chosen value.

You don't have to provide views, in fabrik we create a query with a join to do this. As soon as you use a database join element fabrik will always add the join in, as the data is contained in another database table. This is known as data normalisation and best practices is that it occurs on keys. There are 2 main reasons for that

1) Unique keys are preferable for quickly indexing the joined table and reducing the query execution time.

2) Using a unique reference that is not editable by the user ensures data integrity. As I said previously - what happens if someone changes their name in your system? suddenly your data in one table no longer reflects the reality of the data stored in the user table.

Now my point is, if you have a different set of lookup lists for each user, for example, it could happen that duplicate values are available.

Except that if you use a primary key as the database joins value then we have designed around this, in that each joined table is aliased individually meaning the where query for the join is not needed. The issue with your set up is still to my mind that you need to select the primary key as the database join element's value

So in order to solve your problem, you simply need to add the supplied "where" clause into the left join statement.

I could turn that on its head and say in order to solve your problem you should use database join elements whose values are the primary key fields in the database table you are joining to.

I cannot see how this could create other problems in fabrik, cause all you need to do is wherever the left join statement is created, you just need to add the supplied where clause of the concacted label.

As it states on the element help text this where statement is purely for filtered the selectable options in the form view. If we alter this many existing sites will NOT work as advertised.

If it was something that I felt was a system flaw then I'd consider adding an option, however, I really believe that your application design is not optimal and is thus causing the behaviour you are seeing.

In allowing such an option into our system we would have to support something which is really not the 'right' way to design applications.

As mentioned: This is only a problem when using concated labels, as this is when you use a left join.

We use the join regardless of whether a concat label is selected or not.

 

[p38]

Ok, it looks like we are not getting anywhere as we have a difference of opinion on the term "lookup lists", so perhaps we can agree to disagree.

I use Fabrik developed completely in joomla to enable front end capuring and management. This is comprehensive and caters 99.9% of my needs except for this one small issue we are grappling with.

Then I have client applications running all over the world on pc's and workstations, speaking directly to the mySQL database as defined by fabrik. So apart from peer to peer comms, I also use ODBC, C# and native PHP to deliver content to local applications, displaying a wealth of information.

So fabrik is a part of a massive global application.

If I used fabrik exclusively then it would be easy to define ID's for simple lookups, as fabrik makes it transparent to the web builder and end user. There is power in this.

However, I use all the fabrik databases on another level that have nothing to do with fabrik, hence our difference of opinion on how lookup lists should work.

I will have to see how I can work around this in our systems and fabrik, as it was a problem that a customer picked up, not us, as we did not notice this at the time.

Lastly, I can mention that you can be proud of fabrik and what it has achieved, and having used it for a short while, has become a stock standard stable of our developement house, including digital signage, tournament systems, custom media players, aviation management systems and lots more. All our projects use advanced mySQL peer to peer database management with fabrik databases and works well.

I plan, over time, to add additional plugins etc to fabrik based on our needs of which I will definately pass on to you should you decide it can benefit all.

Many thanks for a great product,

Paul