Have to run mysql query twice to get latest data

Appsmith Support
1

I’m having an issue that may be a mysql issue, but I am a bit out of troubleshooting ideas so posting here.

I have a button that runs a number of queries in sequence when clicked. Here is what it does:

  1. INSERT into media_notes table
  2. three queries that INSERT associations to the previous entry into three different associative tables (people_media_notes, places_media_notes, pevents_media_notes)
  3. SELECT against the media_notes table with JOINs that also return columns listing the associations made in the other tables in the previous queries.

When the button is clicked, all queries execute successfully with no errors. #1 and #2 add the new data as expected, but #3 returns results that don’t reflect the added associations. If I then go to the #3 query in the appsmith editor and hit Run, the results do show the associations as desired.

Again, maybe a MYSQL thing, but almost seems like appsmith is running the #3 query before the #2 queries are done. Any ideas? Is there a way to insert a wait between queries in the onClick JS code to test out if #3 is getting run too soon?

Here is the onClick code:

{{add_book_cons_note.run(() => { showAlert('Note successfully added.','success'); (MULTI_add_cons_note_people.selectedOptionValues.length > 0 ? add_note_people.run() : "");	(MULTI_add_cons_note_places.selectedOptionValues.length > 0 ? add_note_places.run() : "");		(MULTI_add_cons_note_events.selectedOptionValues.length > 0 ? add_note_events.run() : "");		
resetWidget('FORM_add_cons_note');
lookup_book_cons_notes.run();	}, () => {showAlert('Note could not be added;','error'); showAlert(add_book_cons_note.data.toString(),'error');})}}

Here is the #3 query (lookup_book_cons_notes):

SELECT mn.id AS "ID", mn.page_citation, note_subject, note_context, note, ma.media_id, mn.media_consumption_id, GROUP_CONCAT(DISTINCT ppl_mn.person_id SEPARATOR ', ') AS 'people_ids', GROUP_CONCAT(DISTINCT plc_mn.place_id SEPARATOR ', ') AS 'place_ids', GROUP_CONCAT(DISTINCT evt_mn.event_id SEPARATOR ', ') AS 'event_ids'
FROM media_notes mn
LEFT JOIN media_consumptions mc ON mc.id = mn.media_consumption_id
LEFT JOIN media_acquisitions ma ON ma.id = mc.media_acquisition_id
LEFT JOIN people_media_notes ppl_mn ON ppl_mn.media_note_id = mn.id
LEFT JOIN places_media_notes plc_mn ON plc_mn.media_note_id = mn.id
LEFT JOIN events_media_notes evt_mn ON evt_mn.media_note_id = mn.id
GROUP BY mn.id
ORDER BY mn.entry_datetime DESC;

Screenshots:

Screen Shot 2022-03-25 at 4.55.36 PM
Screen Shot 2022-03-25 at 4.55.36 PM1224×596 53.2 KB

2

@trebuchet I believe this issues should resolve if you chain queries sequentially so that query#3 is run after #2.

Here’s some reference material - Creating Workflows - Appsmith

3

This, did it. Thanks! I had to use a combination of the techniques because I wanted all of the queries in #2 to run in parallel and only once they were done, have the #3 query run. Here is what I put together, which seems to work after an initial few passes:

{{add_book_cons_note.run().then(() => 
			((MULTI_add_cons_note_people.selectedOptionValues.length > 0 ? add_note_people.run() : "").then(() =>
			(MULTI_add_cons_note_places.selectedOptionValues.length > 0 ? add_note_places.run() : "").then(() =>
			(MULTI_add_cons_note_events.selectedOptionValues.length > 0 ? add_note_events.run() : "").catch(() => {showAlert('Event(s) could not be associated with note.','error'); showAlert(add_note_events.data.toString(),'error');})).catch(() => {showAlert('Place(s)) could not be associated with note.','error'); showAlert(add_note_places.data.toString(),'error');})).catch(() => {showAlert('Person(s) could not be associated with note.','error'); showAlert(add_note_people.data.toString(),'error');})).then(() =>
					lookup_book_cons_notes.run().then(() => {showAlert('Note successfully added.','success');resetWidget('FORM_add_cons_note');}))).catch(() => {showAlert('Note could not be added;','error'); showAlert(add_book_cons_note.data.toString(),'error');})}}
2 Likes