4

When I run this query, I experience n+1 issue where data JDBC fetches all the related entities of the campaign objects. Is there any way to avoid this using data JDBC?

@Query(
    """
  SELECT campaign.*
  FROM campaign
           JOIN targeting ON campaign.targeting_id = targeting.id
  WHERE (
    CASE
        WHEN campaign.applications_close_date IS NOT NULL
            THEN NOW() BETWEEN campaign.start_date AND campaign.applications_close_date
        WHEN campaign.end_date IS NOT NULL
            THEN NOW() BETWEEN campaign.start_date AND campaign.end_date
        ELSE NOW() >= campaign.start_date
        END
    )
  AND NOT EXISTS
    (
    SELECT *
     FROM application
     WHERE application.campaign = campaign.id
      AND application.influencer = :influencerId 
    )
  """
  )
  fun findAllMatchingByInfluencerId(
    influencerId: Long,
    country: String?
  ): List<Campaign>
Chinez
  • 551
  • 2
  • 6
  • 29
Said K.
  • 43
  • 5

1 Answers1

2

Yes, you can.

If you don't need the referenced entities you should use a class that doesn't have those properties as a return value.

If you actually want those referenced entities, but have a more efficient way to construct the entity with its references you may specify your own ResultSetExtractor or RowMapper in the @Query annotation.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I'm trying to use RowMapper and having the troubles with mapping one-to-many relationship. I can't inject the repository into my RowMapper to fetch all related entities by ID, console says: Illegal arguments for constructor; nested exception is java.lang.IllegalArgumentException: No argument provided for a required parameter: parameter #0 repository of fun (io.promoty.core.campaign.repository.Repository): io.promoty.core.campaign.rowMapper.RowMapper How would you solve this issue? – Said K. Jan 15 '21 at 10:58
  • This is a separate question, please create it as such, so others have a chance of finding it and the answer. – Jens Schauder Jan 15 '21 at 12:40