About sync_rules bucket parameters

[JCKodel]

My scenario: I have a users table, a companies table and a company_users table where I add users to companies.

My sync_rules.yaml is:

bucket_definitions:
  user_data:
    parameters: 
      - SELECT token_parameters.user_id as user_id
      - SELECT company_id FROM company_users WHERE user_id = token_parameters.user_id
    data:
      - SELECT * FROM users WHERE id = bucket.user_id
      - SELECT * FROM user_settings WHERE user_id = bucket.user_id
      - SELECT * FROM company_users WHERE user_id = bucket.user_id
      - SELECT * FROM companies WHERE owner_id = bucket.user_id or id = bucket.company_id

Problem is: a user can be in more than one company, so SELECT company_id FROM company_users WHERE user_id = JWT can return more than one result.

Given the bucket data fetches all companies where id equals the company_id above, it will return all companies, only the first, the whole thing will fail?

If this requires an IN where clause, how it can be written?

Documentation is very vague in sync_rules regard =\

Thanks in advance

[stevensJourney]

You can have multiple parameter queries in the same bucket definition, but the parameter names must be the same over all of them, which is not the case here since user_id != company_id.

Our dashboard should show an error if these sync rules were input there. But we currently don't have linting for self hosted cases.

You should be able to achieve your desired result by implementing two bucket definitions:

  user_data:
    parameters: 
        - SELECT token_parameters.user_id as user_id
    data:
        - SELECT * FROM users WHERE id = bucket.user_id
        - SELECT * FROM user_settings WHERE user_id = bucket.user_id
        - SELECT * FROM company_users WHERE user_id = bucket.user_id
        - SELECT * FROM companies WHERE owner_id = bucket.user_id
        
  user_companies:
    parameters: 
        - SELECT company_id FROM company_users WHERE user_id = token_parameters.user_id
    data:
         - SELECT * FROM companies WHERE id = bucket.company_id

[JCKodel]

@stevensJourney Thank you for your response, but one doubt remains:

What if SELECT company_id FROM company_users WHERE user_id = token_parameters.user_id returns 5 companies?

SELECT * FROM companies WHERE id = bucket.company_id being = will work how? Or do I need to use an IN expression?

[stevensJourney]

It's encouraged to return multiple SQL rows results from bucket_definition parameters queries. Each returned result will be used to create an instance of the bucket definition. In your example if 5 companies are returned then there would be 5 instances of the user_companies sync bucket. Each bucket will have its data content derived using the queries specified in the data section of the bucket definition.

The data query:

 data:
         - SELECT * FROM companies WHERE id = bucket.company_id

Will be evaluated in each bucket with bucket.company_id being the value defined by the returned results from the parameter query.

The user will see all the sync buckets' data. Which will implicitly join the data.

[JCKodel]

@stevensJourney Thank you very much. Now it's crystal clear.