Helo with mJoin Link
Helo with mJoin Link
rpmccormick
Posts: 107Questions: 13Answers: 0
The following example seems to work fine in terms of displaying everything I need, however, in the JSON I see the data for all records in the DB, not just the ones matching the Link(). As the dataset gets bigger, this function is taking forever. How can I fix this?
->join(
Mjoin::inst( 'Booked_Extras' )
->link( 'TripDB.TripID', 'Booked_Extras.TripID' )
->fields(
Field::inst( 'Booked_Extras.Name' )
->options( 'Booked_Extras', 'TripID', 'Name' )
->set(false)
->name('Name'),
Field::inst( 'Booked_Extras.Price' )
->options( 'Booked_Extras', 'TripID', 'Price' )
->set(false)
->name('Price'),
Field::inst( 'Booked_Extras.Qty' )
->options( 'Booked_Extras', 'TripID', 'Qty' )
->set(false)
->name('Qty')
)
->where('TripDB.TripID', 'Booked_Extras.TripID', '=')
)
->where('OrderID', '1234-5678', '=')
->process( $_POST )
->json();
I only want the Extra's for all the Trips of that one Order.
I recently added the ->where('TripDB.TripID', 'Booked_Extras.TripID', '=')
...but it didn't help.
This discussion has been closed.
Answers
I think I fixed it just by getting rid of the ->options() line. Not sure why I thought I needed that before. It still take a while to load the table though. I hope it doesn't continue to get worse any longer as the Extra's table grows.
Another issue... the above is getOrderTrips.php. I could never get it to work for saving edits to the main non-joined table, so instead I have a separate setOrderTrips.php which is the same without the mjoin's.
It used to be that when I set it, it would return the main-table, and everything would be fine except the display of the mjoin stuff would disappear. Now all of a sudden, when I add a new Trip to the Order table, and then edit its Tip field, setOrderTrips returns nothing and the row disappears.
NOTE: I am using in-line edit of the Tip field in the Trip's table of the pending Phone-Order.
If I simply reload the page, then all the edited trips come back, with the Tip edit that was made and all. Really strange is I can then edit the Tip again, and it works fine (returning data, keeping the row, and only losing the mjoin stuff).
Obviously I would love it if I could make getOrderTrips.php just always work so that the mjoins don't disappear, but I could never get that to work and setOrderTrips.php has been a fine work around until this strangeness started happening.
@allen, please come to my rescue
How can I use Editor with lots of mjoins properly?
Hi,
You only posted this thread yesterday. I'd appreciate if you could wait at least 24 hours before bumping a thread - there are about 50 support requests per day I need to reply to, so it takes time. If it is urgent for you, priority support is available.
Good to hear that you got the performance issue fixed at least in part. You are correct that the
where()line for theMjoinisn't going to help since that is what the join is already doing. Beyond that, I would need to be able to profile the PHP with your dataset to be able to understand why it is taking a while.Perhaps you could show me the full PHP and JS code you were using please.
I think I would need to be able to debug the page directly to understand what is going wrong here. Although I guess if the previous issue is resolved then this goes away.
Thanks,
Allan
Ok sorry Allen. I will give much longer than 10 hours next time. I apriciate your support on my issues with Editor.
So here are the 2 issues again:
1) I didn't even know tables with mjoin could be edited. It has always given me an error that seemed to indicate it was not supposed to work. If I can use a table with an mjoin (with set(false) of course) to edit the root table, that would be awesome as right now whenever I edit using the separate file (same but without mjoins) then a bunch of my data disappears until reload.
NOTE: In typing the above, I realized that I use a VIEW (TripDB) for the main file, but save to Booked_Trips... so that's why I can't do it... the VIEW. I had to make views because I could never figure out how to link so much stuff together using your PHP library alone. This sort of thing has been a problem for the past 2 years of development using your tools and I still don't understand how to do it right.
2) In one (repeatable) case ever, and a recent bug that didn't exist before: On new phone order, when you just added a single row to the TripDB table, and then you edit the TIP in Booked_Trips using inline edit... instead of just the mjoin data disappearing (as expected) the entire row disappears (the AJAX edit call actually returns an empty dataset). If you then reload the page, the trip re-appears (with the proper edit performed). If you then do the exact same edit of TIP, the problem is gone (the AJAX edit call returns all the Booked_Trips fields, and only the mjoin stuff disappears)
You are welcome to see it all in action in my backend (you were there once before about 6 months ago, but that old account is deleted by now... we just launched a week ago and much cleaning was done). Please create an account here, and then I will upgrade it to Admin so you can see what I am talking about: https://onewaylimo.com/signup/passenger/
If this ends up needing a lot of your time, I will contact the client and see if I can get them to pay for some priority support. Let me know.
Now for the files:
1) getOrderTrips.php
2) setOrderTrips.php
3) VIEW definition for TripDB
1) getOrderTrips.php
2) setOrderTrips.php
3) VIEW definition for TripDB
They can and they can't. I wouldn't recommend it! It is possible with the PHP libraries to edit a many joined table, but you need to submit the values for the whole row in a single field. This is not something that the GUI supports without a specific and custom developed field type plug-in. If you don't do that there is significant risk of the data being destroyed. For this reason you won't find any examples of it on this site.
If the data is originally coming from the view, which I hadn't realised before, then you would need to query the view to get the full data for the row again. Perhaps the easiest way of doing that would be to use
ajax.reload()inside asubmitCompleteevent handler.I'm sorry to hear that the pre-built libraries don't do everything you need. They never will provide the full flexibility of SQL - that simply isn't a goal of those libraries. They are designed to make 80% of use cases as easy as possible. The remaining 20% (roughly) would require some custom code, possibly not using the pre-built libraries at all any instead using your own code for your specific use case.
For example using a VIEW to get the data as you want it is a very good way of populating the table.
It sounds like you have updated form 1.4 or earlier to 1.5 and not taken account of the fact that only the field value that was changed is now submitted. There is upgrade documentation about that available here. My guess is that if you use
submit: 'allIfChanged'it will restore what you expect.Allan
There is still some strange issue. I have added submit: 'allIfChanged';
...and now the row doesn't disappear, however, the field I change still renders wrong...
When the trip is added, the value is 0 and displays CASH. If I inline-edit the value and change it to 10.00, I briefly see $10.00 and then it changes back to CASH which is wrong even according to the JSON sent back:
If I then reload the page it is now properly showing $10.00.
If I then change it to 11.00 it works properly displaying $11.00.
...so again, something is different on first edit after adding a new row (by my own sql methods) and calling refresh, then on an editing a row after refreshing the entire page.
It's like the row is inline editing properly, but then when it auto-refreshes it is doing it from a different (maybe non-existent) field, instead of the field it just properly edited.
At a guess it sounds like the create might not be including the id, or there is an event handler that is otherwise updating the field.
I've just created an account at the link you gave above with my name and details. Could you upgrade that to admin as you mentioned and let me know how I can see this error in the app so I can debug it? Feel free to PM that info if you'd rather it not be public!
Allan
I sent you a PM, and purchased a support contract.
For others reading this thread, this is my latest idea:
"A fix might simply be specifying Field for each row instead of letting the code determin field-name automatically.... how do you do that again?"
Hi,
Thanks for the PM - I've replied back there. I'll update this thread when we've resolved the issue in the PM.
Regards,
Allan