Hello!
I hope you are GREAT!
I wanted to see if some of you have had the same issues that I have when I upload any project to the server.
Nº 1 Module Relationships Many to Many
For example I have a relationship many to many with Blog and Blog Categories.
Suppose I associate blog_category_id_1 to blog_id_1, blog_id_2 and blog_id_3
When I open the ASSOCIATE WITH BLOG CATEGORIES on Blogs/show/4, in the DROPDOWN, it shows to me !!! 4 times !!!
the blog_category_id_1.
So I fixed the query _fetch_available_options_many_to_many on Module Relations adding GROUP BY
function _fetch_available_options_many_to_many($data) {
//return records from the 'alt_module' that have not yet been assigned to THIS particular module
$sql = 'SELECT
[alt_module].*,
[relation_name].[calling_module]_id as calling_module_id

FROM
[relation_name]
RIGHT OUTER JOIN
[alt_module]
ON
[relation_name].[alt_module]_id = [alt_module].id
GROUP BY
[alt_module].id
';
Then, I was really happy because on LOCALHOST it was fixed! I could see only one record for each category.
But, when I've uploaded this to the SERVER, Online it BROKES the thing and stops showing the ASSOCIATE WITH BLOG CATEGORY Button. So, I had to take the GROUP BY out to make this work again.
The same thing happens to me with the calendar.
It works fine on LOCALHOST but it don't appears online, on the server.
I don't understand how servers works, but maybe any of you could say to me what can I try or do.
Cheers
Claudia

Hi Claudia,

Can you please compare versions of MySQL, PHP and Apache from local host to your live server, also Trongate Framework version?

The issues you are having is a bit strange. I tried to replicate it but I'm not getting any of those problems you have mentioned.

Also, have you tried adding a DISTINCT key word to the query?

Yikes! That's a stinker.

The only way to solve that kind of issue, I think, is to recreate it. So, you're probably gonna have to upload some code to GitHub - along with some db table creation SQL.

Then, it's a case of having somebody go through the code to see if they get the same result.

I understand that you may not want to upload your entire app to GitHub. I assume that what you're doing is private. However, if you can upload the bare minimum to repeat the fault then I think it'll be easier to solve.

Try RIGHT JOIN instead of RIGHT OUTER JOIN

They are essentially the same but it's possible that the database version/type on your server doesn't support RIGHT OUTER JOIN usage.

Side note: Am I right in thinking that your query pulls some records, then you use PHP to filter those records to find the ones you actually want?
If so, think about ways that you can get the database to do that filtering work for you. Always aim to return the exact data you need from your query as databases are much faster at handling/processing data than PHP and your resulting code also will be much cleaner.

Hello!
Thank you for the answers.
DiSTINCT don't make any difference on Localhost nor the Server
I've Uploaded the try to github https://github.com/cserquis/blog and on the server it can be seen on www.blog.lawebpage.com
Now I've took out the GROUP BY, for you to see the duplicates results on https://www.blog.lawebpage.com/blog_notices/show/2 .
The thing is that with or without RIGHT OUTER JOIN, on my server, when I add GROUP BY, on Localhost is OK but on the SERVER the query is giving this error, so it is not haven´t the files from the SQL.
Uncaught SyntaxError: Unexpected token < in JSON at position 0
at JSON.parse ()
at XMLHttpRequest.http.onload (admin.js:146:28)
I've sent the question to my server to see if they can help me.
I'll let you know if I can have a solution, otherwise, I will have to use PHP to filter those records to find the ones I actually want :)
Cheers!
Claudia

Ah OK so the error is in the conversion to JSON. That's a whole different matter.

This implies that the data in your remote DB is not exactly the same as in your local DB and that some aspect of the remote data is corrupting the conversion to JSON.

Update: In fact, you'd be better off making sure your json_encode() call doesn't return false before you send it:

replace:



with:

Hi Claudia,

I notice you are using version: 1.3.3030 which is a few revisions behind, not sure that's the issue here

Could you tell me what version of stack the server is running? You can find it easily on the home screen of phpMyAdmin

I've installed your app and all seems to be running fine on my system with Apache/2.4.5, MariaDB 1.3.3030 and PHP 8.1.4

I'll keep poking about to see if I can replicate your problem

Edit.. OK I've removed the group by from the many to many SQL and now I'm getting repeating drop down options.. I'll keep looking, nothing seams out of place at the moment.

Hi Claudia,

I think this might be a bug with the many to many relationships... I've created a site on my server which has a barebones app install. A blog and category module with a many to many relationship.

https://ums.myds.me/help_bar/

As you can see I'm getting the same repeating dropdown at https://ums.myds.me/help_bar/blogs/show/4

I'll look into this a bit deeper when I get some more time...

ps. I know adding GROUP BY to the many to many SQL solves the issue for me too locally and on my server (not sure why your server is giving you that json error - have you tried D3mon's suggestion above?)

I have noticed this same issue with many to many relationships, in that when you click 'associate with XXX' you can get multiple listings of the same record.

I have also noticed that when you set up the relationship and say the view should be ordered by say Title Asc, this isn't replicated in the dropdowns, when you do the association.

Hi John, yeah I remember someone else bringing this up in the past, now that you mention it. Also, the reason it is not sorting in the many-to-many dropdown, is there is no ORDER BY in the query.

Claudia, I may have a solution, :) be it simple. I've been playing in the API using David's Post Person module and have discovered that if the bridging table of the many-to-many relationship has only one ID record for the alt_module. You don't get repeats in the drop-down, but if there are more than one ID record, you do get repeating items in the dropdown.

Non-repeating dropdown:



Repeating dropdown:



So, my fix is to simply query the alt_module and add an ORDER BY to fix John's mention above of it not being sorted. I know the GROUP BY works too, but this is a much better query for what we need for the options dropdown and I don't think it affects any of David's other code (I hope I'm right here?).