[SOLVED] Union query in database join dropdown element + SOLUTION from Hugh

marcq · Sep 26, 2016

Hi,

I can't solve this issue by myself so I would need a quote from you creating an union query.

Issue :

Customer are creating bookings in frontend
a) A query is checking the available time slots and only the available start time are displayed into the dropdown
b) the starttime value is saved into the "book_starttime" field of the "fab_booking" table.
Administrator can edit the bookings and change the booking day, booking start time.
a) When editing the already saved value is displayed into the dropdown field.

The Database Join Dropdown should display the already saved value + the available time slots start time for the given day.

Needed :
I tried my self to create the needed UNION query without success :

Code:

WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id )) UNION (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND t.id = '{fab_booking___book_starttime}' AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))

I would need a quote from you to write this union query since it is to complicate for me.

Database Join Dropdown Element | Data-Where tab is empty for now so that you can edit the form. For all details please see "My Sites" : La Neptune.

Thank you in advance for your quote.

Cheers,

Marc

PS :

This is the query retrieving the available time slots start time :

Code:

WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))

Union Query should "union" this query with a query retrieving the actual in the table saved value. I tried several version but was unable to get a result.

cheesegrits · Sep 26, 2016

Have you tried asking on Stack Exchange?

Looks to me like you have your parens in the wrong place. If I pretty print that in Navicat, it looks like ...

Code:

SELECT
    *
FROM
    date_test
WHERE
    { thistable }.id IN (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    NOT (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND p1.book_date = '{fab_booking___book_bookingdate}'
                GROUP BY
                    t.id
            )
    )
UNION
    (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND t.id = '{fab_booking___book_starttime}'
                AND p1.book_date = '{fab_booking___book_bookingdate}'
                GROUP BY
                    t.id
            ) AS x
        WHERE
            nbre = (
                SELECT
                    count(p2.book_date)
                FROM
                    fab_booking_taken AS p2
                WHERE
                    p2.book_date = x.book_date
            )
    )

... and the UNION looks like it's two indents left of where it should be, so the closing parens after that first inner SELECT are in the wrong place.

Which site is this on? And do I have phpMyAdmin access?

And can you give me values for the various placeholders which should produce some results, so I can test it?

-- hugh

marcq · Sep 27, 2016

Hi Hugh,

Thank you for your reply. Indeed, I tried on several forums but without success.

Concerned site is in My Sites : la Neptune, you will find some explanations into the Notes.

Yes you have phpMyAdmin access.

Concerned tables are fab_booking, fab_booking_slots and fab_booking_taken.

The guy who helped me on a french forum

http://www.developpez.net/forums/d1...s-entre-06-30-minuit-reservation/#post8749644

to create this query seems to be absent since more than 2 weeks. I'm stuck, can't come forward with the project, so I need really help.

His last tip was that I need to create a query that returned the already saved value, then union this query with the query that returned the available time slots.

Query that returned the available slots :

Code:

WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))

And that the column names and numbers needs to be the same.

It is honestly to complicate for me at this stage and I would appreciate your support.

Thank you in advance Hugh,

Cheers, marc

marcq · Oct 3, 2016

Hi Hugh,

As told before I'm willing to pay to solve this issue rapidly since I'm stuck and I can't get any help elsewhere.

I just need to know if you can provide me some help here or not.

Thank you in advance for your reply.

Cheers, marc

cheesegrits · Oct 4, 2016

OK, I spent a while playing aorund with the query, and this seems to work:

Code:

{thistable}.id IN (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    NOT (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
                GROUP BY
                    t.id
            ) as foobar2
UNION
    (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND t.id = '{fab_booking___book_starttime}'
                AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
                GROUP BY
                    t.id
            ) AS x
        WHERE
            nbre = (
                SELECT
                    count(p2.book_date)
                FROM
                    fab_booking_taken AS p2
                WHERE
                    p2.book_date = x.book_date
            )
    )
)

Main problem was (as I said in my last post) the parens around the parts of the union selects were in the wrong place. But also, your p1.book_date is in Y-m-d (no time) format, but the boo_bookingdate includes the time, so you have to DATE_FORMAT() that. Also MySQL complaining that the first select needed to be named (hence "as foobar2").

It seems to work, except the first row (logged in as the user you gave me to test with) doesn't seem to select the "already selected" slot. The rest do, however.

Let me know ...

-- hugh[/code]

marcq · Oct 5, 2016

Hi Hugh,

Thank you for your support and time.

The problem wasn't in fact to get the available slots, the query to get the slots is the same that the one I have implemented on frontend :
http://www.webamazingapps.com/projects/neptunegeneve.ch/index.php

Tab : "Location"
Field : "Heure de d?part"

which is working fine.

But to get the available slots + the actual saved value.

For example if you have chosen the record 11 (id) from the list "G?rer les r?servations La Neptune", starttime of the booking is "08:00", so we should also have this value displayed (actual starttime saved value).

My problem is that I need to have the available slots + the actual saved value dispayed into the dropdown.

cheesegrits · Oct 6, 2016

Yes, I know. And as far as I can tell, it's doing that. For instance ...

http://www.yoursite.com/projects/neptunegeneve.ch/index.php/gerer-reservations-ln-pa/form/21/1

(replace 'yoursite')

The selected departure for that row is 8am, which shows on the dropdown along with the available slots.

http://screencast.com/t/r5VYbbKCUTg

-- hugh

marcq · Oct 7, 2016

Sorry Hugh, I missunderstood your explaination ! Of course it works, great work, thanks a lot for your time, expertise and dedication. Cheers, marc

marcq · Oct 7, 2016

Solution provided by Hugh :

Code:

{thistable}.id IN (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    NOT (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
                GROUP BY
                    t.id
            ) as foobar2
UNION
    (
        SELECT
            id + 3
        FROM
            (
                SELECT
                    p1.book_date,
                    t.*, count(p1.book_date) AS nbre
                FROM
                    fab_booking_taken AS p1
                CROSS JOIN fab_booking_slots AS t
                WHERE
                    (
                        (
                            t.heuredepart_resa < p1.book_end
                            AND t.heurearrivee_resa > p1.book_start
                        )
                    )
                AND t.id = '{fab_booking___book_starttime}'
                AND p1.book_date = DATE_FORMAT('{fab_booking___book_bookingdate}', '%Y-%m-%d')
                GROUP BY
                    t.id
            ) AS x
        WHERE
            nbre = (
                SELECT
                    count(p2.book_date)
                FROM
                    fab_booking_taken AS p2
                WHERE
                    p2.book_date = x.book_date
            )
    )
)

cheesegrits · Oct 7, 2016

As told before I'm willing to pay to solve this issue rapidly

Drop me an email, hugh dot messenger at gmail dot com.

-- hugh

[SOLVED] Union query in database join dropdown element + SOLUTION from Hugh

marcq

Member

cheesegrits

Support Gopher

marcq

Member

marcq

Member

cheesegrits

Support Gopher

marcq

Member

cheesegrits

Support Gopher

marcq

Member

marcq

Member

cheesegrits

Support Gopher

Useful links

Members online