Doing 4 way filter based on 3 tables using GORM

I’ve been trying to achieve a 4 way to join/filter based on 4 tables “Offers”, “UserPaymentMethods” and a junction table “OffersUserPaymentMethods” defined as below;

So I want to filter “offers” based on payment_method_id because offer_id lives in offers_user_payment_methods which makes it a bit tricky. Front-end will send payment_method_id and I need to filter offers based on the payment_method_id that’s it.

CREATE TABLE `offers_user_payment_methods` (
  `offer_id` bigint(20) unsigned NOT NULL,
  `user_payment_method_id` bigint(20) unsigned NOT NULL
)

CREATE TABLE `offers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_uid` longtext NOT NULL,
  `base` varchar(20) NOT NULL,
  `quote` varchar(20) NOT NULL,
  `side` longtext NOT NULL,
  `price` decimal(32,16) NOT NULL,
  `origin_amount` decimal(32,16) NOT NULL,
  `available_amount` decimal(32,16) NOT NULL,
  `min_order_amount` decimal(32,16) NOT NULL,
  `max_order_amount` decimal(32,16) NOT NULL,
  `payment_time_limit` bigint(20) unsigned NOT NULL,
  `state` longtext NOT NULL,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL
)

CREATE TABLE `user_payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_uid` longtext NOT NULL,
  `payment_method_id` bigint(20) unsigned DEFAULT NULL,
  `data` json DEFAULT NULL,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
)

CREATE TABLE `payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` longtext NOT NULL,
  `bank_name` longtext NOT NULL,
  `logo` longtext NOT NULL,
  `options` json DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL
)

Answer

You will struggle to do this efficiently and entirely with Gorm. Preloading/associations aren’t done using joins in Gorm and there is no way to filter based on them. I see two potential options:

1. Write your own query using joins and scan in the results

You can use Gorm for the query and execution, but honestly, I would just avoid all the need for reflection etc and just define a struct and scan straight into that.

The results will contain duplicated data, so you will have to manually transpose the results and build up the object.

3. Execute two queries, one to find the IDs of the offers, and one to find the offers

The first query would be the equivalent of:

SELECT offers_user_payment_methods.offer_id FROM offers_user_payment_methods
INNER JOIN user_payment_methods ON offers_user_payment_methods. user_payment_method_id = user_payment_methods.id
WHERE user_payment_methods.payment_method_id = ?

If you scan these results into var offerIDs []int, you can use Gorm to find the offers by passing this slice as the param:

offers := make(Offer, 0)
db.Find(&offers, offerIDs)

I think this solution has the benefit of you do the more complex query and leave the easy stuff to Gorm (which is what it does ~ok).