Re: Ignite Affinity - sql join Question

[Jiang Jacky]

Please exactly use this example to do that.
You don't need PesonKey class
https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java

Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: MJ <6733...@qq.com>
Sent: Tuesday, June 13, 2023 1:24:48 AM
To: user <user@ignite.apache.org>
Subject: Re: Ignite Affinity - sql join Question

No luck.  With the change its throwing exception BinaryObjectImpl cannot be 
cast to java.lang.String  when it tried to put a new entey into "companies" 
cache.

Beside, there is already the @AffinityKeyMapped annotation on companyId field 
of PersioKey class.  AffinityKey needs to be marked on sql join's both left and 
right sides?

Thanks,
MJ

---Original---
From: "Jiang Jacky"<jiang0...@gmail.com>
Date: Tue, Jun 13, 2023 12:34 PM
To: "user@ignite.apache.org"<user@ignite.apache.org>;
Subject: Re: Ignite Affinity - sql join Question

Hello
You have to set your company configuration with affinity key


CacheConfiguration<AffinityKey<String>, Company> companyCfg = 
newCacheConfiguration<>("companies");


Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: MJ <6733...@qq.com>
Sent: Monday, June 12, 2023 10:46:23 PM
To: Chandranana Naik via user <user@ignite.apache.org>
Subject: Ignite Affinity - sql join Question

hi Igniters,

Can you pls advise below ?

It always prints out below WARN message in server side when I am trying to use 
the sql join .  Anything wrong with my configuration or testing code ? Is it 
possible to eliminate that WARN message ?



[ WARN] org.apache.ignite.internal.processors.query.h2.QueryParser - For join 
two partitioned tables join condition should contain the equality operation of 
affinity keys. Left side: PERSON; right side: COMPANY



Ignite version: 2.13.0

see below test code, which is extracted from 
https://ignite.apache.org/docs/latest/data-modeling/affinity-collocation and 
added with some additional annotation changes.



public class AffinityCollocationExample2 {



    static class Person {

        @QuerySqlField(index = true)

        private int id;

        @QuerySqlField(index = true)

        private String companyId;

        @QuerySqlField

        private String name;



        public Person(int id, String companyId, String name) {

            this.id = id;

            this.companyId = companyId;

            this.name = name;

        }



        public int getId() {

            return id;

        }

    }



    static class PersonKey {

        private int id;



        @AffinityKeyMapped

        private String companyId;



        public PersonKey(int id, String companyId) {

            this.id = id;

            this.companyId = companyId;

        }

    }



    static class Company {



        @QuerySqlField(index = true)

        private String id;

        @QuerySqlField

        private String name;



        public Company(String id, String name) {

            this.id = id;

            this.name = name;

        }



        public String getId() {

            return id;

        }

    }



    public void configureAffinityKeyWithAnnotation() {

        CacheConfiguration<PersonKey, Person> personCfg = new 
CacheConfiguration<PersonKey, Person>("persons");

        personCfg.setBackups(1);

        personCfg.setIndexedTypes(PersonKey.class, Person.class);

        personCfg.setSqlSchema("pojo");



        CacheConfiguration<String, Company> companyCfg = new 
CacheConfiguration<>("companies");

        companyCfg.setBackups(1);

        companyCfg.setIndexedTypes(String.class, Company.class);

        companyCfg.setSqlSchema("pojo");



        Ignite ignite = Ignition.start();

        IgniteCache<PersonKey, Person> personCache = 
ignite.getOrCreateCache(personCfg);

        IgniteCache<String, Company> companyCache = 
ignite.getOrCreateCache(companyCfg);



        Company c1 = new Company("company1", "My company");

        Person p1 = new Person(1, c1.getId(), "John");



        // Both the p1 and c1 objects will be cached on the same node

        personCache.put(new PersonKey(p1.getId(), c1.getId()), p1);

        companyCache.put("company1", c1);



        // Get the person object

        p1 = personCache.get(new PersonKey(1, "company1"));



    }



    public static void main(String[] args) throws Exception {

        AffinityCollocationExample2 main = new AffinityCollocationExample2();

        main.configureAffinityKeyWithAnnotation();



        
System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++");

        main.test_sql_join_affinity();

        System.exit(0);

    }



    public void test_sql_join_affinity() throws ClientException, Exception {

        String sql = "select a.companyId, b.name from pojo.Person a,  
pojo.Company b where a.companyId = b.id";



        ClientConfiguration cfg = new 
ClientConfiguration().setAddresses("127.0.0.1:10800");



        try (IgniteClient client = Ignition.startClient(cfg)) {

            SqlFieldsQuery query = new SqlFieldsQuery(sql);



            List<List<?>> rets= client.query(query).getAll();

            for(List<?> e : (List<List<?>>)rets) {

                System.out.println(e);

            }

        }

    }

}