1. Java / Говнокод #19480

    −35

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    import org.jooq.Field;
import org.jooq.Record;
import org.jooq.Record2;
import org.jooq.RecordMapper;
import org.jooq.SelectLimitStep;
import org.jooq.SelectOnConditionStep;
import org.jooq.Table;
import org.jooq.impl.SQLDataType;

...

	@SuppressWarnings("unchecked")
	@Override
	public List<MWithdrawal> loadLastOfEveryUsers(Integer count) {
		Table<Record2<Integer, Integer>> ids_numbered_by_user = dsl
				.select(rowNumber().over().partitionBy(WITHDRAWAL.USER_ID).orderBy(WITHDRAWAL.REQUEST_DATE.desc()).as("number_by_user"),
						WITHDRAWAL.ID.as("withdrawal_id"))
				.from(WITHDRAWAL).asTable("ids_numbered_by_user");
		SelectOnConditionStep<Record> numbered_by_user = dsl.select().from(ids_numbered_by_user).join(WITHDRAWAL)
				.on(WITHDRAWAL.ID.eq((Field<Integer>) ids_numbered_by_user.field("withdrawal_id")));
		Field<Integer> numbered_by_user_user_id = (Field<Integer>) numbered_by_user.field(WITHDRAWAL.USER_ID.getName());
		Field<Integer> numbered_by_user_number = (Field<Integer>) numbered_by_user.field("number_by_user");
		Table<?> last_unprocessed = dsl.select(numbered_by_user_user_id, max(numbered_by_user_number).as("last_unprocessed_number_by_user"))
				.from(numbered_by_user).where(((Field<Boolean>) numbered_by_user.field(WITHDRAWAL.PROCESSED.getName())).eq(Boolean.FALSE))
				.groupBy(numbered_by_user_user_id).asTable("last_unprocessed");
		Field<Integer> last_unprocessed_user_id = (Field<Integer>) last_unprocessed.field(WITHDRAWAL.USER_ID.getName());
		Field<Integer> last_unprocessed_number_by_user = (Field<Integer>) last_unprocessed.field("last_unprocessed_number_by_user");
		Field<Integer> numbered_by_user_withdrawal_id = (Field<Integer>) numbered_by_user.field("withdrawal_id");
		Table<Record2<Integer, Integer>> withdrawal_ids = dsl.select(numbered_by_user_withdrawal_id, numbered_by_user_number).from(numbered_by_user)
				.leftOuterJoin(last_unprocessed).on(last_unprocessed_user_id.eq(numbered_by_user_user_id))
				.where(numbered_by_user_number.le(count)
						.or(last_unprocessed_number_by_user.isNotNull().and(numbered_by_user_number.le(last_unprocessed_number_by_user))))
				.asTable("withdrawal_ids");
		Field<Integer> withdrawal_id = (Field<Integer>) withdrawal_ids.field("withdrawal_id");
		SelectLimitStep<Record> select = dsl.select().from(WITHDRAWAL).join(withdrawal_ids).on(withdrawal_id.eq(WITHDRAWAL.ID)).join(CURRENCY)
				.on(CURRENCY.ID.eq(WITHDRAWAL.CURRENCY_ID)).join(CURRENCY_CONFIG).on(CURRENCY_CONFIG.ID.eq(WITHDRAWAL.CURRENCY_ID)).join(_USER)
				.on(_USER.ID.eq(WITHDRAWAL.USER_ID)).leftOuterJoin(WITHDRAWALS_TXIDS)
				.on(WITHDRAWALS_TXIDS.field(TRANSACTION_TXID.WITHDRAWAL_ID).eq(WITHDRAWAL.ID))
				.orderBy(WITHDRAWAL.USER_ID.asc(), WITHDRAWAL.REQUEST_DATE.desc());

		List<MWithdrawal> list = select.fetch().map(createGroupRecordMapper());
		return list;
	}

    Легким движением руки sql превращается, sql превращается... Превращается sql... В элегантный код!

    with numbered_by_user as ( select row_number() over (partition by user_id order by request_date desc) as number_by_user, w.* from withdrawal w )
    select n.* from numbered_by_user n left join ( select user_id, max(number_by_user) last_unprocessed_number_by_user from numbered_by_user where
    processed = false group by user_id ) last_unprocessed on last_unprocessed.user_id = n.user_id where n.number_by_user <= 4 or
    last_unprocessed.last_unprocessed_number _by_user is not null and n.number_by_user <= last_unprocessed.last_unprocessed_number _by_user;

    Запостил: indvd00m, 18 Февраля 2016

    Комментарии (3) RSS

    Добавить комментарий