0

I'm trying to get a list of my tv_shows based on Actor Id.

At this moment, this is the only thing I get working (The list of chapters based on Actor Id)

I have tried to do the same with TvShows, but I'm getting an error.

So I assume, I can't access from Actor to TvShow without creating a new table.(I don't want to create another table. And I'm not sure if I think in the correct way :D)

I have a database with Tables: tv_shows > seasons > chapters

actors and chapters_has_actors Check the EER Diagram of the Database

TvShow.java

@Entity
@Table(name = "TV_SHOWS")
public class TvShow implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME")
    private String name;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "tvShow")
    private List<Season> seasons;

// Getters and Setters
}

Season.java

@Entity
@Table(name = "SEASONS")
public class Season implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "TV_SHOW_ID", nullable = false)
    private TvShow tvShow;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "season")
    private List<Chapter> chapters;

// Getters and Setters
}

Chapter.java

@Entity
@Table(name = "CHAPTERS")
public class Chapter implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SEASON_ID", nullable = false)
    private Season season;
    
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable (name = "CHAPTERS_HAS_ACTORS",
            joinColumns = @JoinColumn(name = "CHAPTER_ID"),
            inverseJoinColumns = @JoinColumn(name ="ACTOR_ID"))
    private List<Actor> actor;

// Getters and Setters
}

So when I go to my ChapterServiceImpl.java and do this >

@Service
public class ChapterServiceImpl implements ChapterService {

    @Autowired
    private ChapterRepository chapterRepository;
    @Autowired
    private ActorRepository actorRepository;

    private ModelMapper modelMapper = new ModelMapper();

    @Override
    public List<ChapterRest> getChaptersByActorId(Long actorId){
        Optional<Actor> actor = actorRepository.findById(actorId);
        List<ChapterRest> chapterList =  chapterRepository.findByActorId(actorId)
                .stream()
                .map(chapter -> modelMapper
                .map(chapter, ChapterRest.class))
                .collect(Collectors.toList());
        return chapterList;
    }

and go to my ChapterControllerImpl.java

@RestController
@RequestMapping("/tv-shows/{tvShowId}/seasons/{seasonNumber}/chapters")
public class ChapterControllerImpl implements ChapterController {

    @Autowired
    private ChapterService chapterService;

    @Override
    @GetMapping(value = "/actors", produces = MediaType.APPLICATION_JSON_VALUE)
    public List<ChapterRest> getChaptersByActorId(
            @RequestParam Long actorId) {
        return 
                chapterService.getChaptersByActorId(actorId));

    }
}

ChapterRepository.java

@Repository
public interface ChapterRepository extends JpaRepository<Chapter, Long> {

    List<Chapter> findByActorId(Long actorId);
}

I get a list of chapters based on my Actor Id

The question is how can I get a list of TvShows and Chapters based on Actor Id? I'm not sure what I'm doing bad, but I can't get a list of my TvShows based on Actor Id

Thank you, sorry for my English and have a great day!

v0ld3m0rt
  • 866
  • 3
  • 12
  • 47
Stage
  • 1
  • 1
  • Hi. take a look at https://stackoverflow.com/questions/8339889/jpa-jpql-select-items-when-attribute-of-item-list-set-contains-another-item You can use inner join of three tables and select distinct TV_SHOWS – Roman Golov Jun 28 '22 at 11:54
  • How can I apply this to my project? – Stage Jun 28 '22 at 13:37
  • Is there a particular reason you don't want to make a new table (Trust me I'd understand if you just don't want to, but it would be nice to know)? To me, a `CAST` table seems useful. Another option is to build a `VIEW` instead of a table and query directly off it. Personally, I think that using JPA for this is a bit extreme. Let the database do some legwork too. – Jetto Martínez Jun 28 '22 at 15:18
  • Just trying to understand Spring. Personally for me would be easier to make another table. But if I do another table finally I would get 2 lists and I don't know how to stream them in my project because I'm noob :) – Stage Jun 28 '22 at 16:07

0 Answers0