How to build dynamic query with ent

Question

I am trying to build a dynamic query with ent., because the searching fields may vary. The relation of the 3 table and SQL query are like below. The results should contains the fields from both Customer table and Order table.

Customer Table
| customer_Id | first_name | last_name | account_id |
|:----------- |:----------:| ---------:| ----------:|

Account Table
| account_id | card_num | payment_id |
|:---------- |:--------:| ----------:|

Order Table
| order_num | product_num | payment_id |
|:--------- |:-----------:| ----------:|

Select customer_Id ,  first_name, last_name, order_num, product_num   
FROM customer c  
INNER JOIN account a ON c.account_id = a.account_id  
INNER JOIN order o ON a.payment_id = o.payment_id  
WHERE c.customer_id = 123 OR c.first_name = ‘Mike’ OR c.last_name = ‘Lee’ OR c.account_id = ‘321’

Because the searching fields will vary, user could provide any one of the data from customer_Id, first_name , last_name, account_id. So I am using ent. Predicate to create a dynamic query.

var predicateCustomer []predicate.Customer  

If searchFields.CustomerId != “” {  
    predicateCustomer = append(predicateCustomer, customer.CustomerIdEQ(searchFields.CustomerId))  
}  

If searchFields.FirstName != “” {  
    predicateCustomer = append(predicateCustomer, customer.FirstNameEQ(searchFields.FirstName))  
}  

If searchFields.LastName != “” {  
    predicateCustomer = append(predicateCustomer, customer.LastNameEQ(searchFields.LastName))  
}  

If searchFields.AccountId != “” {  
      predicateCustomer = append(predicateCustomer, customer.AccountIdEQ(searchFields.AccountId))  
}  

// Approach 1
item, err := e.client.Customer.Query().
    Where(predicateCustomer...)
    Limit(30).
    Offset(0).
    All(ctx)

// Approach 2
item, err := e.client.Customer.Query().
    Where(predicateCustomer...).QueryAccount().QueryOrder().
    Limit(30).
    Offset(0).
    All(ctx)

My current issue is the 1st approach will only return fields from Customer table, and the 2nd approach will only return fields from Order table. What's the query should be if I want to have the fields from both Customer and Order table?

Answer 1

YOu can achieve the desired output by uing the joins similar to sql query you provided in your current code. Here is the updated code, give it a try;

var predicateCustomer []predicate.Customer

if searchFields.CustomerId != "" {
    predicateCustomer = append(predicateCustomer, customer.CustomerIdEQ(searchFields.CustomerId))
}

if searchFields.FirstName != "" {
    predicateCustomer = append(predicateCustomer, customer.FirstNameEQ(searchFields.FirstName))
}

if searchFields.LastName != "" {
    predicateCustomer = append(predicateCustomer, customer.LastNameEQ(searchFields.LastName))
}

if searchFields.AccountId != "" {
    predicateCustomer = append(predicateCustomer, customer.AccountIdEQ(searchFields.AccountId))
}

item, err := e.client.Customer.Query().
    Where(predicateCustomer...).
    WithAccount().
    WithOrder().
    Limit(30).
    Offset(0).
    All(ctx)

You can  include associated Account and Order entities in the query by using the WithAccount() and WithOrder() functions. The item variable will hold the combined results of the query, which retrieved fields from the Customer and Order tables.

Verify that the edges in the ent schema have been defined to accommodate these connections between the Customer, Account, and Order tables.

Hope it works :)