Gets all the notes left by anyone for a service that is registered as yours via Service.user_id

get_notes_for_your_services.sql

SELECT Note.created ucreated, Note.id FROM customers Customer JOIN services Service JOIN notes Note ON (Service.customer_id=Customer.id AND Note.service_id=Service.id) WHERE (Note.user_id=1 OR Service.user_id=1)

# I really want to expand this to include notes left by anyone for a customer that has a service belonging to you (yet the note is customer specific not service specific via a HasMany relationship)

The original statement that does everything including not-associated-to-service notes:

SELECT * FROM (
(SELECT Note.id FROM customers Customer
    JOIN services Service ON Service.user_id={$cuid} AND Service.customer_id = Customer.id
    JOIN notes Note ON Note.customer_id = Customer.id AND Note.service_id=Service.id
    {$conditions[1]})
UNION
(SELECT Note.id FROM notes Note WHERE Note.user_id={$cuid}{$conditions[0]})
) tmp_tbl
JOIN notes Note ON Note.id = tmp_tbl.id
JOIN services Service ON Service.id = Note.service_id
JOIN customers Customer ON Customer.id = Note.customer_id
JOIN users User ON User.id = Note.user_id
ORDER BY $order
$t_limit

The hacky count query:

SELECT count(id) Count FROM (
(SELECT Note.id FROM customers Customer
    JOIN services Service ON Service.user_id={$cuid} AND Service.customer_id = Customer.id
    JOIN notes Note ON Note.customer_id = Customer.id AND Note.service_id=Service.id
    {$conditions[1]})
UNION
(SELECT Note.id FROM notes Note WHERE Note.user_id={$cuid}{$conditions[0]})
) tmp_tbl