Al-Mothafar Al-Mothafar - 7 months ago 68
Java Question

using Ebean, fill property value of entity list stored another related entity property

I'll try to describe my question with codes directly and will say what I need after, note that I minimized my code to ficus on the issue that I have:

I get list of feed from Entity :

@Entity
public class Feed {

@Id
@Column
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

@ManyToOne
@JoinColumn(name = "event_id")
private Event event;

@ManyToOne
@JoinColumn(name = "event_trigger_id")
private EventTypeTrigger eventTrigger;
}


Here I'm trying to get this Entity filled with correct
count
value.

@Entity
public class EventTrigger {

@Id
@Column
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

@Column(nullable = false)
private String title;

@Transient
private int count;
}


The feed entity that store the count data.

@Entity
public class EventTriggerFeed {
@Id
@Column
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;


@Column(nullable = false)
private int count;

@ManyToOne
@JoinColumn(name = "event_id", nullable = false)
@JsonBackReference
private Event event;

@ManyToOne
@JoinColumn(name = "event_trigger_id", nullable = false)
@JsonBackReference
private EventTrigger eventTrigger;
}


And from service that I try to call:

public List<Feed> findUserFeeds(int userId) {
return Ebean.find(Feed.class).findList();
}


I can get count for each Trigger like this :

public int getCountFromEventTriggerFeed(int triggerId, int eventId) {
return Ebean.find(EventTriggerFeed.class)
.where().eq("eventTrigger.id",triggerId).eq("event.id",eventId)
.findUnique().getCount();
}


I simply want to get list of
Feed
, that have
Event
info and
EventTrigger
info, and with
count
property filled with count value from
EventTriggerFeed


I just kinda stuck with perfect way without so much iteration, i'm thinking about get the count value for each
EventTrigger
inside
Feed
row, but it could lead me to a lot of calls:

for (Feed feed : findUserFeeds(userId)) {
int eventId = feed.getEvent().getId();
int triggerId = feed.getEventTrigger().getId();
feed.getEventTrigger().setCount(getCountFromEventTriggerFeed(triggerId, eventId))
}


But can you imagine if I have like 50 feeds?

Is there any idea about how to optimize this headache?

Answer

I solved this with creating view in .sql file something like this:

CREATE OR REPLACE VIEW `event_feed_summary_view` AS
  SELECT
    max(feed.trigger_count) * up.amount AS total_amount,
    feed.trigger_count,
    uf.feed_id,
    feed.event_id,
    feed.event_trigger_id,
    up.amount
  FROM event_trigger_feeds feed
    INNER JOIN event_type_triggers trig ON feed.event_trigger_id AND trig.id
    INNER JOIN user_feed uf ON uf.event_id = feed.event_id AND uf.event_trigger_id = feed.event_trigger_id
  GROUP BY uf.feed_id, feed.event_id, feed.event_trigger_id;

I placed this code inside conf/db_views/create_views.sql

And I called this file on started up inside my Module OnStartup :

@Singleton
public class OnStartup {

    @Inject
    public OnStartup(final Configuration configuration, final Environment environment, Database db) {


        // Create views
            Logger.info("Creating / Replacing the Tables views in database");
            InputStream is = environment.classLoader().getResourceAsStream("db_views/create_views.sql");
            String sql;
            try {
                sql = IOUtils.toString(is);
                if (sql != null) {
                    Connection conn = db.getConnection();
                    try {
                        Statement stmt = conn.createStatement();
                        try {
                            stmt.execute(sql);
                            Logger.info("Views Created");
                        } finally {
                            try {
                                stmt.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    } catch (SQLException e) {
                        Logger.error("Could create Statement ");
                        e.printStackTrace();
                    } finally {
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (IOException e) {
                Logger.error("Could not find create view script");
                e.printStackTrace();
            }
    }
}

with Ebean I get the result with SqlQuery :

public long getTotal(int feedId) {
    String sql = "select sum(total_amount) as total from event_feed_summary_view where feed_id = :feed_id;";
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("feed_id", feedId);
    SqlRow r = sqlQuery.findUnique();
    if (r != null && r.getLong("total") != null) {
        return r.getLong("total");
    }
    return 0;
}
Comments