library(RMySQL)
library(tidyverse)
yhendus<-dbConnect(dbDriver("MySQL"), user="root",
                   password="root", host="localhost", dbname='film')
paring<-dbSendQuery(yhendus, 
                    "SELECT tabel2.people_id, tabel2.film_id  
 FROM  efis_film_role_person AS tabel1 JOIN
      efis_film_role_person AS tabel2 ON tabel1.film_id=tabel2.film_id
          WHERE tabel1.people_id=1018 
          AND tabel1.language='et' AND tabel2.language='et'")
filmiandmed=fetch(paring, n=-1)
head(filmiandmed)
isikupaarid <- filmiandmed %>% inner_join(filmiandmed, by="film_id") %>%
   filter(people_id.x<people_id.y)
head(isikupaarid)
write_csv(isikupaarid %>% select(people_id.x, people_id.y), 
            "D:\\r\\1004\\isikupaarid1.csv", col_names=FALSE)


#Asendage isikute koodid ees- ja perekonnanimedega
paring<-dbSendQuery(yhendus, 
                    "SELECT id, first_name, last_name FROM efis_people")
isikuandmed=fetch(paring, n=-1) %>% 
    unite("kogunimi", first_name, last_name, sep=" ")
head(isikuandmed)
write_csv(isikupaarid %>% inner_join(isikuandmed, by=c("people_id.x"="id")) %>%
     inner_join(isikuandmed, by=c("people_id.y"="id")) %>%
     select(kogunimi.x, kogunimi.y), "D:\\r\\1004\\isikupaarid2.csv",
     col_names=FALSE)

vastus=tibble(isik1=character(), isik2=character())
vastus <- vastus %>% add_row(isik1="Juku", isik2="Kati")
vastus