left join query

A

Andyt78

New Member
The result of the query below is:

Team1 Team2 Goals_T1 Goals_T2
Brasile Croazia 0 2

SELECT teamh.name as Team1,
teamg.name as Team2,
game.g_teams_score as Goals_T1,
game.h_teams_score as Goasl_t2
FROM iew1r_sportspredictions_games game
left join iew1r_sportspredictions_teams teamh
on game.h_teams_id = teamh.id
left join iew1r_sportspredictions_teams teamg
on game.g_teams_id = teamg.id

How can I reproduce it in Fabrik?

I did following:
---------

I created a new List (connection to iew1r_sportspredictions_games table)

Join Type: Left
From: iew1r_sportspredictions_games / to:iew1r_sportspredictions_teams
From column: h_teams_id / To column: id

Join Type: Left
From: iew1r_sportspredictions_games / to:iew1r_sportspredictions_teams /
From column: g_teams_id / To column: id

Then I selected elements I want to be displayed
---------

The result is not like expected. Only one Join Table is visible.
Team1 Team2 Goals_T1 Goals_T2
Brasile Brasile 0 2



Thanks for support
 
Can you
- post screenshots of your join settings, the element list of your list

- enable fabrik debug (in fabrik options, "Debug" tab), then append &fabrikdebug=1 to your list URL
A click on "list GetData:your list" should show the generated MySQL query
 
Hi,

attached you will find the screenshots you requested.
I don?t understand your instructions.

I appended the &fabrikdebug=1 to my list url pressed enter but I can?t find any button to click. What do you exactly mean with:

A click on "list GetData:your list" should show the generated MySQL query
 

Attachments

  • Elemens1.jpg
    Elemens1.jpg
    75.2 KB · Views: 335
  • Join1_.jpg
    Join1_.jpg
    17.4 KB · Views: 338
When you display any Fabrik front end page with fabrikdbug=1 appended to the URL as a query string variable, we will output an expandable table of detailed debug info, including the query we are generating the form or list data from.

So just display your list (the actual list, not the settings) on the front end, and append &fabrikdebug=1 (or ?fabrikdbug=1 if you are using SEF'ed URL's and there are no other QS variables).

If you have also enabled the global setting (Debug: yes), you should see some very obvious debug data.

-- hugh
 
The underlying link is still going to localhost and using the http.... text it's loading indefinitely.
 
I don't think you have debug enabled in fabrik's global configuration - could you enable that?

Note that's not Joomla's debug settings, but Fabrik's own one.

thanks
Rob
 
There's a typo in your link (fabrikdbug instead of fabrikdebug), so fabrikdebug isn't activated.
But I can't get this link (corrected) finishing loading.
Can you disable the Joomla System debug?
 
I copied the string from post #4!!
Now I have the debug table but there is no reaction by clicking on it.
http://andytondi.no-ip.biz:81/AndyHp/index.php/2014-01-13-22-31-19/partite?fabrikdebug=1

I could find the query in the source file. The second left join table name is "iew1r_sportspredictions_teams_0" but Fabrik query never use it. In the attached PDF you can see how it should be. I put the original query form Fabrik and my modified query.
 

Attachments

  • Query.pdf
    171.6 KB · Views: 296
your joomla template is generating a js error which is stopping the debug toggling from working : Uncaught TypeError: Cannot call method 'getElements' of nulltemplates/j51_resonate/js/dropdown.js

SELECT * FROM `iew1r_sportspredictions_games`
LEFT JOIN `iew1r_sportspredictions_teams` AS `iew1r_sportspredictions_teams` ON `iew1r_sportspredictions_teams`.`id` = `iew1r_sportspredictions_games`.`h_teams_id`
LEFT JOIN `iew1r_sportspredictions_teams` AS `iew1r_sportspredictions_teams_0` ON `iew1r_sportspredictions_teams_0`.`id` = `iew1r_sportspredictions_games`.`g_teams_id`

So we're all on the same page, above is the summarize query (I've removed all the select fields to make the query more readable) . so the join part of query is the same as the one you posted in your original question. The only difference is that we alias tables when they are used in more the one join (as in this case) so we know which table is which.

However, the select fields them selves are all using iew1r_sportspredictions_teams and not both iew1r_sportspredictions_teams and iew1r_sportspredictions_teams_0 which is probably the issue.
If possible can you supply an ftp account in http://fabrikar.com/you/my-sites/ (only staff can see the details) so I can log in and debug why this is happening. Also could you confirm if you are using the latest code from github or the latest zip release?
thanks
Rob
 
Sorry this one fell off my radar.
So I looked, theres only one result as there is only one record in the table iew1r_sportspredictions_games
However, I guess that's because this is test data?
Anyway I'm not really sure what it is you want to do, should you be selecting existing teams when adding records, or should you be creating a new team with each new record?
Can you describe in general what it is that you hope to achieve?
Thanks
Rob
 
After one week waiting this is not exactly the answer I was expecting. I thought the problem was clear. (See previous post #13).

The problem is not that there is only one record or because of test data. In the output record data are missing. If the select fields would use "iew1r_sportspredictions_teams_0" generated form left join, then we would have a different result (See previous post #12 PDF attachment). I think it?s clearly a coding problem.
 
Well I thought that I did understand the question, then I re-looked at your site and didn't understand what it was you wanted to achieve. Hence why I asked the questions in post #16 - if you could reply to those then I can move forward with the issue. I'm still not sure if its a code issue or if its an implementation issue, as I don't know what you are trying to implement.
 
I gave you access to my DB and my Site. I think the best thing is you execute the below query by your self. The result of the query is what I want form Fabrik. For more explanation please have a look at the att. PDF file. I think with this explanation you should have no doubts anymore!

SELECT SQL_CALC_FOUND_ROWS DISTINCT `iew1r_sportspredictions_teams`.`name` AS `Team1` , `iew1r_sportspredictions_teams_0`.`name` AS `Team2` , `iew1r_sportspredictions_games`.`h_teams_score` AS`Goal Team1` , `iew1r_sportspredictions_games`.`g_teams_score` AS `Goal Team2`
FROM `iew1r_sportspredictions_games`
LEFT JOIN `iew1r_sportspredictions_teams` AS `iew1r_sportspredictions_teams` ON `iew1r_sportspredictions_teams`.`id` = `iew1r_sportspredictions_games`.`h_teams_id`
LEFT JOIN `iew1r_sportspredictions_teams` AS `iew1r_sportspredictions_teams_0` ON `iew1r_sportspredictions_teams_0`.`id` = `iew1r_sportspredictions_games`.`g_teams_id`
LIMIT 0 , 30
S
 
I've seen the queries, and I wouldn't ask additional questions if I didn't feel an answer would help me help you.

I can see what you have done, I'm just not convinced that what you have done is what you want to achieve, the query doesn't give me an answer to that. I understand its frustrating but if you could answer this one question I have it would really help me:

"Should you be selecting existing teams when adding records or should you be creating a new team with each new record?"
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 139 (members: 1, guests: 138)
    Back
    Top