Comments (5)
I also tried to use a native SQL. In this way, only one query is executed:
val employees = useConnection { conn ->
val sql = """
select emp.id as emp_id, emp.name as emp_name, act.id as act_id, act.desc as act_desc, act.created_at as act_time
from employee emp left join (
select a.id, a.employee_id, a.desc, a.created_at
from activity a
where a.id = (
select max(b.id)
from activity b
where b.employee_id = a.employee_id
)
) act on emp.id = act.employee_id;
""".trimIndent()
conn.prepareStatement(sql).use { statement ->
statement.executeQuery().use { rs ->
rs.iterable().map { row ->
Employee {
id = row.getLong("emp_id")
name = row.getString("emp_name")
latestActivity = Activity {
id = row.getLong("act_id")
desc = row.getString("act_desc")
createdAt = row.getTimestamp("act_time")?.toLocalDateTime()
}
}
}
}
}
}
from ktorm.
It could be easier if the id
in activity
table is an auto-increment key.
I tried to write some code. In this way three queries are executed:
// select ... from employee
val employees = Employees.findAll()
val employeeIds = employees.map { it.id }
// select activity.employee_id, max(activity.id)
// from activity
// where activity.employee_id in (?, ?, ...)
// group by activity.employee_id
val latestActivityIds = Activities.asSequence()
.filter { it.employeeId inList employeeIds }
.groupingBy { it.employeeId }
.eachMaxBy { it.id }
// select ... from activity where activity.id in (?, ?, ...)
val activities = Activities.findMapByIds(latestActivityIds.values.filterNotNull())
for (employee in employees) {
val activityId = latestActivityIds[employee.id] ?: continue
employee.latestActivity = activities[activityId]
}
from ktorm.
I should have mentioned that the IDs are auto incrementing primary keys. The first solution is a good solution for someone like me who isn't very comfortable writing raw queries.
I was doing some reading on how people solve this problem and came across this article that talks about how this could be achieved using just a single query and keeping it efficient as well using subqueries
.
Although this article is written for PHP (and for Laravel framework), I think this could be added in ktorm as well. I found other examples online where subqueries make writing complex queries very intuitive and keeps it within ORM boundary.
Here is the article (you don't have to read the whole article if you don't want to, you can stop when it starts talking about macros but I highly recommend reading the whole thing as you might find a gem or two to add in ktorm): https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries
from ktorm.
Thank you. This is a good article. In fact, I also have a plan to support subqueries. The final usage should be similar to that in this article.
from ktorm.
Great! I would be very happy to test it out on a real database once ready. Any idea about when it might be available?
from ktorm.
Related Issues (20)
- [Feature Request]:Field update strategist, pre positioned during insertion and update
- Connectivity problem HOT 1
- 关于join的优化
- Multiple onConflict at bulkInsertOrUpdate HOT 1
- MySQL存储UUID报错 HOT 8
- 自定义支持MySQL的find_in_set函数 HOT 5
- 适配 solon框架 HOT 1
- Union功能存在问题? HOT 2
- 关于列绑定,能不能先references再transform?
- 🧐为什么不添加支持分页的函数呢 HOT 2
- Oracle Timestamp support
- Database#exceptionTranslator should return a nullable throwable
- 4.0版本使用KSP生成实体类运行出错 HOT 8
- ksp column converter in version 4.0.0 disappeared HOT 1
- Ktorm 别名机制似乎有问题 HOT 2
- [Feature Request]: Support OffsetDateTime for Postgresql
- [Feature Request]: KSP Table Annotation Support Specify super class for the table generated HOT 1
- Relaxing BaseTable related visibility constraints
- Postgres how to use TextArray's Contains method? HOT 1
- 使用实体查询,框架会拼接字段名称为【表别名_字段】而遭到 ColumnNameChecker 字段名称过长拦截 HOT 6
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from ktorm.