まったり技術ブログ

Technology is power.

Specificationインタフェースを利用した副問合せ

Specificationインタフェースを利用した副問合せに関して、あまり情報が見当たらなかったので、メモ程度にφ(・ω・ )。

サンプルテーブル

よく見かける 1対多 の関係で説明していきます。
f:id:motikan2010:20171013000832p:plain:w400

ユーザ テーブル

サンプルコード

下記のサンプルコードでは、「同じ内容のツイートを3回以上しているユーザ」を選択という少し面倒くさいSQL文を発行してみます。
f:id:motikan2010:20171014134003j:plain

ファイル構成

 ── dbapp
    ├── DbappApplication.java
    ├── model
    │   ├── Tweet.java
    │   ├── Tweet_.java
    │   ├── User.java
    │   └── User_.java
    ├── repository
    │   ├── TweetRepository.java
    │   └── UserRepository.java
    └── spec
        └── BadUserSpec.java

Specification

今回の肝となる部分です。

BadUserSpec.java
package com.motikan2010.dbapp.spec;

import com.motikan2010.dbapp.model.Tweet;
import com.motikan2010.dbapp.model.Tweet_;
import com.motikan2010.dbapp.model.User;
import com.motikan2010.dbapp.model.User_;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

public class BadUserSpec implements Specification<User> {

    @Override
    public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        final List<Predicate> predicates = new ArrayList<>();

        // サブクエリ
        Subquery<Tweet> subquery = query.subquery(Tweet.class);
        Root<Tweet> subRoot = subquery.from(Tweet.class);
        subquery.select(subRoot.get(Tweet_.user.getName()));
        subquery.where(cb.equal(root.get(User_.id.getName()), subRoot.get(Tweet_.user.getName())));
        // ツイート内容でグループ化
        subquery.groupBy(subRoot.get(Tweet_.body.getName()));
        // 条件 
        subquery.having(cb.and(
                cb.greaterThanOrEqualTo(cb.count(subRoot), 3L)
        ));

        predicates.add(cb.exists(subquery));

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

}

エンティティ(モデル)

User.java
package com.motikan2010.dbapp.model;

import lombok.Data;

import javax.persistence.*;
import java.util.List;

@Entity
@Data
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private int id;
    
    @Column(name = "nickname")
    private String nickname;

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Tweet> tweetList;

}
Tweet.java
package com.motikan2010.dbapp.model;

import com.sun.istack.internal.NotNull;
import lombok.Data;

import javax.persistence.*;

@Entity
@Data
@Table(name = "tweet")
public class Tweet {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private int id;
    
    @Column(name = "body")
    private String body;

    @NotNull
    @Column(name = "user_id")
    private int userId;
    
    @ManyToOne(targetEntity=User.class)
    @JoinColumn(name = "user_id", referencedColumnName = "id", insertable=false, updatable=false)
    private User user;

}

メタモデル

恥ずかしながらメタモデルの存在を今まで知らなかった。。
JPAを深掘りする〜Criteria APIで型安全な検索を追求しよう!【基本編】 - 技術ブログ | 株式会社クラウディア
ちなみにエンティティと同じパケッケージに所属させないといけないらしい。
別のパッケージに配置し、ヌルポと格闘したのはいい思い出・・・。

stackoverflow.com

User_.java
package com.motikan2010.dbapp.model;

import javax.persistence.metamodel.ListAttribute;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(User.class)
public class User_ {
    public static volatile SingularAttribute<User, Integer> id;
    public static volatile SingularAttribute<User, String> nickname;
    public static volatile ListAttribute<User, Tweet> tweetList;
}
Tweet_.java
package com.motikan2010.dbapp.model;

import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(Tweet.class)
public class Tweet_ {
    public static volatile SingularAttribute<Tweet, Integer> id;
    public static volatile SingularAttribute<Tweet, String> body;
    public static volatile SingularAttribute<Tweet, User> user;
}

リポジトリ

今回は空っぽ。

package com.motikan2010.dbapp.repository;

import com.motikan2010.dbapp.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User>{
}

呼び出し側

DbappApplication.java
package com.motikan2010.dbapp;

import com.motikan2010.dbapp.model.User;
import com.motikan2010.dbapp.repository.UserRepository;
import com.motikan2010.dbapp.spec.BadUserSpec;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

@SpringBootApplication
public class DbappApplication implements CommandLineRunner {

    @Autowired
    UserRepository userRepo;

    public static void main(String[] args) {
        SpringApplication.run(DbappApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {

        BadUserSpec spec = new BadUserSpec();
        List<User> userList = userRepo.findAll(spec);

        for(User user : userList){
            System.out.println(user.getId() + " : " + user.getNickname());
        }

    }

}

発行SQL

select user0_.id as id1_1_, user0_.nickname as nickname2_1_ from user user0_ where exists (select tweet1_.user_id from tweet tweet1_, user user2_ where tweet1_.user_id=user2_.id and user0_.id=tweet1_.user_id group by tweet1_.body having count(tweet1_.id)>=3)

分かりやすくするとこんな感じ。

select id , nickname 
from user user1 
where exists (
    select user_id 
    from tweet, user user2 
    where tweet.user_id = user2.id and user1.id = tweet.user_id 
    group by body 
    having count(tweet.id)>=3
)

いい感じに副問い合わせが行えている。
countの部分をsumやavgに変えるなどして様々なパターンに応用できそう。

PHPBrewに"intl"拡張をインストール

2017/11/07 ※追記 こちらのやり方が簡単!!

qiita.com
できなかったよ。という方が本記事の方法も試してみてもいいかと。

動作環境

バージョン
macOS 10.12.6
PHPBrew 1.22.6
PHP 7.1.0

ことの始まり

CakePHP3を入れたのだが、起動ができない。
intlを有効にしないといけないらしい。

$ bin/cake server -p 8765

PHP Fatal error:  You must enable the intl extension to use CakePHP.
 in /Users/admin/PhpstormProjects/cake3app/config/requirements.php on line 31

Fatal error: You must enable the intl extension to use CakePHP.
 in /Users/admin/PhpstormProjects/cake3app/config/requirements.php on line 31

問題発生

// インストール有無の確認
$ phpbrew ext | grep intl
 [ ] intl

// 拡張intlをインストール
$ phpbrew ext install intl

//・・・

Error: Command failed: /usr/bin/make -C '/Users/admin/.phpbrew/build/php-7.1.0/ext/intl' 'all'  >> '/Users/admin/.phpbrew/build/php-7.1.0/ext/intl/build.log' 2>&1 returns:
The last 5 lines in the log file:
/usr/local/Cellar/icu4c/59.1/include/unicode/unistr.h:3180:7: error: delegating constructors are permitted only in C++11

      UnicodeString(Char16Ptr(buffer), buffLength, buffCapacity) {}

      ^~~~~~~~~~~~~

2 warnings and 3 errors generated.

make: *** [intl_convertcpp.lo] Error 1

エラー_(:3 」∠ )_

解決方法

下記の記事を参考にした。
Install the PHP INTL extension on a Mac

$ cd /Users/admin/.phpbrew/build/php-7.1.0/ext/intl/
$ vim Makefile
CXXFLAGS = -g -O2
↓に変更(33行目付近:"-std=c++11"を追記)
CXXFLAGS = -g -O2 -std=c++11

// ビルド
$ make
$ make install
Installing shared extensions:     /Users/admin/.phpbrew/php/php-7.1.0/lib/php/extensions/no-debug-non-zts-20160303/

// .soファイル生成の確認
$ ls -l /Users/admin/.phpbrew/php/php-7.1.0/lib/php/extensions/no-debug-non-zts-20160303/ | grep intl
-rwxr-xr-x  1 admin  staff   512764  9 19 01:16 intl.so

// "intl.ini"ファイルの作成と、1行記述
$ vim ~/.phpbrew/php/php-7.1.0/var/db/intl.ini
extension=intl.so

intlインストールの確認

intlが有効になっていることを確認

$ phpbrew ext | grep intl
 [*] intl         1.1.0

動作確認

CakePHP3も動作していることを確認できた。

$ bin/cake server -p 8765
Welcome to CakePHP v3.5.0 Console

Zend Framework2をHerokuで動かす

最近ZF2をさわり始めたので、メモ程度に書いてみる。
ちなみにHerokuはHTTPS前提なのでFacebookのOAuth認証といった要HTTPSの動作確認で重宝した。

Zend Framework2 インストール

MVC Skeleton Application - Install - Zend Framework

$ composer create-project -n -sdev zendframework/skeleton-application ZF2

Procfileの作成

Customizing Web Server and Runtime Settings for PHP | Heroku Dev Center
ドキュメントルートを「/public」に設定する為、下記のコマンドで「Procfile」を作成します。

echo "web: vendor/bin/heroku-php-apache2 public/" > Procfile

Herokuへデプロイ

デプロイの際に「composer.lock」が.gitignoreファイルに含まれていないことを確認してください。
herokuがcomposer.lock必須になったのでcomposerの入れ方をメモしておく - KayaMemo

$ heroku create
$ git add .
$ git commit -m "first commit."
$ git push heroku master

動作確認

$ heroku open

f:id:motikan2010:20170806185032j:plain
終わり…。「Procfileの作成」の部分が普段と違うところだろうか。